I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: Filtering Numeric data from a character based column

Often you can see a request made by people to help them separate data in thier character based columns to numeric and non-numeric part.

Most often the answer they get is very simple: Use IsNumeric built in function.

But there's a very little known fact about this function though:

It DOES NOT behave the way you think it does!

You don't believe me? Let's see with an example. How many rows do you think this will return:

 

DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d45' UNION ALL
SELECT '123e45' UNION ALL
SELECT '12 3456' UNION ALL
SELECT '123456' UNION ALL
SELECT '5532.673'

SELECT   *
FROM     @t1
WHERE    isnumeric(title) = 1

 

If you said 2 you're wrong. Run it. It return exactly 4 rows. You see IsNumeric returns 1 for every input that can be converted to

integer, floating point, money or decimal. This means that it also supports Scientific (E) notation.

In scientific notation 123D45 is 123*(10^45) in double precision and 123E45 is 123*(10^45) in single precision, which means that both are valid numbers.

 

I bet that right about now you're thinking: "I just want to filter my data!! Why does it have to be so complicated????" :))

No fear, the answer is here :).  This function will give you exactly what you need:

 

CREATE FUNCTION dbo.IsNumericEx( @value nvarchar(max) )
RETURNS BIT
AS
BEGIN
         DECLARE @isInt BIT

         SELECT  @isInt = 1 
         WHERE   @value NOT LIKE '%[^0-9.-]%'
                 AND LEN(REPLACE(@value, '.', '')) >= LEN(@value) - 1
                 AND CHARINDEX('-', @value) IN (0, 1)
                 AND CHARINDEX('-', @value, 2) = 0

         RETURN ISNULL(@isInt, 0)
END
GO

DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d456' UNION ALL
SELECT '12 3456' UNION ALL
SELECT '123456' UNION ALL
SELECT '1234-56' UNION ALL
SELECT '123456-' UNION ALL
SELECT '-123456' UNION ALL
SELECT '-123-456' UNION ALL
SELECT 'dddfaf56' UNION ALL
SELECT '5532.673' UNION ALL
SELECT '5532673.' UNION ALL
SELECT '.5532.673'

SELECT   *
FROM     @t1
WHERE    dbo.IsNumericEx(title) = 0

SELECT   *
FROM     @t1
WHERE    dbo.IsNumericEx(title) = 1

GO
DROP FUNCTION dbo.IsNumericEx

 

Enjoy it!

 

kick it on DotNetKicks.com
 

Print | posted on Thursday, October 18, 2007 6:00 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

Hi Mladen -- good stuff, but what about commas?

I usually find it is simple to replace 'e' and 'd' with 'x' or something like that before using isnumeric:

select title, isnumeric(replace(replace(title,'d','x'),'e','x')) as IsNumber
from @t1

Works pretty well.
10/19/2007 3:21 PM | Jeff
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

what about commas?? commas are irrelevant :))

seriously though if someone needs that check it can be easily added.
10/19/2007 3:23 PM | Mladen
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

Commas er very important.
If you think outside the US.

Here in europe we use comma as decimal point, and decimal point as 1000mark
So we have to do code for testing on the comma and removing the dot. as the DOT is irellevant here.

Long live country standards... not..

Also same thing with Dateformat, it is the biggest problem with database coding..
10/30/2007 8:16 AM | Rolf Thomassen
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

tell me about it... i am from europe :)

however all this is not a problem if you use parameters, which in reality is just wishfull
since there are tons and tons of legacy apps that don't use them. :)
10/30/2007 11:18 AM | Mladen
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

hehe, rant away :)))
10/30/2007 10:25 PM | Mladen
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

guys,
where exactly is Europe? I know somewhere around north pole...
11/2/2007 10:33 PM | Johny The Rip
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

I don't think using a function like this on a large dataset is something you'd want. But if you DO; I would try to 'shortcut' the function before executing all kinds of replaces etc.

In Pseudocode:

Function IsNumericEx(someNum)
If IsNumeric then
...code posted in this blogpost
else
return false
End Function
11/19/2007 5:30 PM | RobIII
Gravatar

# re: SQL Server: Filtering Numeric data from a character based column

hi all

i wanna to take the number after "dot" in decimal at SQLServer

for example

the decimal 1234.5 here i wanna just to got the number after "dot"
here it's 5

and thank advanced
5/25/2009 10:08 AM | hamzah
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET