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 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.

Leave a Reply

Your email address will not be published.