Tag Archives: isnumeric

T-SQL

T-SQL: Spaces in the ISNUMERIC function

If you’ve ever used the ISNUMERIC function in T-SQL, you’ll know that it returns a 1 if a value passed to it can be evaluated as numeric, and a 0 if not.  For example:-

ISNUMERIC(123) --> 1
ISNUMERIC('123') --> 1
ISNUMERIC('abc') --> 0
ISNUMERIC('123.456') --> 1
ISNUMERIC('12/24') --> 0 (even though it looks like a fraction, it isn't)

What might surprise you then is that…

ISNUMERIC('         123     ')

….evaluates to 1, and is therefore considered numeric, which means that spaces before and after the value are ignored.  If there are spaces inbetween numbers however, you’ll get a return of 0:-

ISNUMERIC('123 456') --> 0

Worth keeping in mind….one of those things that might catch you out if you’re not aware of it.