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 is decimal
DECLARE @input VARCHAR(10)='102030.40' SELECT ISNUMERIC(@input) IsNumber
3) Input 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.