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!
