Check If String Value Has Numeric Data Or Not In SQL

In this blog, you will learn how to check string value has Numeric Data or not in SQL

Herewith, I have shared my analysis and added the solutions. order to check the varchar field for the mathematical calculation whether the varchar field value has numeric data or not.

 

We are storing the numeric and string value in the varchar. For example, $500. If we use the Isnumeric, it will return true only. In order to avoid this kindly of mirror issue, we can use the try_Cast, It will return false only.

 

When string value has this character € | + | . | , | \ | - | 12e4 |

• isnumeric return result 1.
• When we using try_Cast it returns 0.

See below another example,

• $1000 is not numeric, but ISNUMERIC returns true, then proceed for the convert it as numeric.
• Now, It says "Error converting data type varchar to numeric"

SQL

DECLARE @var varchar(100)
SET @var = '$1000' SELECT ISNUMERIC(@var)
SELECT CASE
WHEN ISNUMERIC (@var) = 1
THEN CAST(@var AS numeric(36, 4))
ELSE CAST('0' AS numeric(36,4))
END

Result

 

ISNUMERIC Return the varchar as True Example

 

 

Solutions

 

In this type of case, while varchar value is used for numeric calculation. Use TRY_CAST 

DECLARE @var varchar(100);
SET @var = '$1000';
SELECT ISNULL( TRY_CAST(@var AS numeric(36, 4)), 0 )