How to check if the input is numeric

ISNUMERIC() – This is a system function which can be used to evaluate the given input, if the input is valid numeric it returns 1 otherwise 0. 

There are times where we need to check if the input value contains any non numeric chars. the ISNUMERIC() can not be used for this purpose as it evaluates numbers, money and decimal to true.

Example:
1)  Input value contains only numbers

DECLARE @input VARCHAR(10)='102030'
SELECT ISNUMERIC(@input) IsNumber
2)  Input value is decimal
DECLARE @input VARCHAR(10)='102030.40'
SELECT ISNUMERIC(@input) IsNumber
3)  Input value is money
DECLARE @input VARCHAR(10)='$102030'
SELECT ISNUMERIC(@input) IsNumber
PATINDEX to check existence of non numeric chars

The PATINDEX function can be used to check if any non numeric char exists in the input.

DECLARE @input VARCHAR(10)='102030.40'
SELECT PATINDEX('%[^0-9]%',RTRIM(LTRIM(@input))) AS IsNumber

This statement returns the position of any chars other than numbers. if the statement returns greater than 0 then the input is not a valid number.