# I want some Moore

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

### 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.

My Books

Users Online:

## 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!

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

## #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

## #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 |

## #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

## #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 |

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

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

## #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

## #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:

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

## #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