Check whether an expression is numeric type in sql serverIntroduction
Sometimes we need to convert an expression in numeric values, but it caused exception if the expression is not a valid numeric type.
Like in the example below
DECLARE @Expression VARCHAR(20)
SET @Expression='123A'
SELECT CONVERT(INT,@Expression)
The last statement cause an exception because it is trying to convert an VARCHAR type to INT.
To avoid this exception its better to check the expression is a valid numeric type.
DECLARE @Expression VARCHAR(20)
SET @Expression='123A'
IF ISNUMERIC(@Expression)=1
SELECT CONVERT(INT,@Expression)
ELSE
SELECT 'Invalid type conversion'
SET @Expression='123'
IF ISNUMERIC(@Expression)=1
SELECT CONVERT(INT,@Expression)
ELSE
SELECT 'Invalid type conversion'
OUTPUT
|
| Invalid type conversion |
| 123 |
Thanks