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
     <span class="kwrd">SELECT</span>  @isInt = 1 
     <span class="kwrd">WHERE</span>   @<span class="kwrd">value</span> <span class="kwrd">NOT</span> <span class="kwrd">LIKE</span> <span class="str">&#39;%[^0-9.-]%&#39;</span>
             <span class="kwrd">AND</span> LEN(REPLACE(@<span class="kwrd">value</span>, <span class="str">&#39;.&#39;</span>, <span class="str">&#39;&#39;</span>)) &gt;= LEN(@<span class="kwrd">value</span>) - 1
             <span class="kwrd">AND</span> CHARINDEX(<span class="str">&#39;-&#39;</span>, @<span class="kwrd">value</span>) <span class="kwrd">IN</span> (0, 1)
             <span class="kwrd">AND</span> CHARINDEX(<span class="str">&#39;-&#39;</span>, @<span class="kwrd">value</span>, 2) = 0

     <span class="kwrd">RETURN</span> 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!