<< SQL Server Hosting - HostForLIFE :: Auto Query Generator In MSSQL Server | European ASP.NET Ajax Hosting :: What is AJAX Security? >>

SQL Server Hosting - HostForLIFE :: Check If String Value Has Numeric Data Or Not In SQL

June 7, 2021 08:53 by author

Peter

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

Check String Value Has Numeric Data Or Not In SQL

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 )