SQL Server ISNUMERIC function is System Functions which is used to check if the expression is valid numeric type or not.
The ISNUMERIC() function accepts an expression and returns 1 if the expression is a valid numeric type else it returns 0.
But in some case ISNUMERIC() does not work as expected for values specially when we have “+”,”-” or “d” in value and have two numbers after “d” such as 123d45, it still return 1.
It also returns 1, if expression have value in money format $123456 , $ as prefix of value.
Syntax
ISNUMERIC(input expression)
input expression
It is any valid expression to be evaluated.
Following are the valid numeric datatype that can be evaluated by ISNUMERIC Functions.
Exact Numerics bigint, int, smallint, tinyint, bit.
Monetary Values smallmoney, money.
Fixed Precision decimal, numeric.
Approximate float, real.
Lets look at an example of ISNUMERIC function in SQL Server.
Select '12345' as expression, ISNUMERIC('12345')AS Is_Numeric Union all Select '$12345' , ISNUMERIC('$12345') Union all Select '12345$', ISNUMERIC('12345$') Union all Select '54f54', ISNUMERIC('54f54') Union all Select '123', ISNUMERIC('123') Union all Select '123.09', ISNUMERIC('123.09') Union all Select 'SQL SERVER', ISNUMERIC('SQL SERVER') Union all Select '-12456', ISNUMERIC('-12456') Union all Select '+12456', ISNUMERIC('+12456') Union all Select '12345d67', ISNUMERIC('12345d67') Union all Select '1.2345', ISNUMERIC('1.2345') Union all Select '1', ISNUMERIC('1')
1,843 total views, 4 views today