Home » SQL Server ISNUMERIC Function

SQL Server ISNUMERIC Function

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')




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.