Search code examples
sqlsql-servertype-conversion

How to convert Varchar column to Numeric

I have a requirement to move varchar column data to Numeric but with two conditions.

  1. All the alphanumeric value should migrate as null
  2. All the decimal values should go as it is.

I wrote the condition as WHERE data like '%[^0-9]%', it is working fine for all the records except for decimal.

Also I have values like .001 abcd, this has to be pass as null.

To summarize I need : 

1) 1234 as 1234
2) 1.23 as 1.23
3) ABC as null
4) .ABC as null
Solution

  • There is by default function in SQL Server ISNUMERIC() so, first of all Check your data value by that function,

    Select ISNUMERIC(DATA)

    Whole query is written as below,

    SELECT CASE WHEN ISNUMERIC(data)=1 THEN CAST(data as decimal(18,2))
            ELSE NULL END as tData FROM DataTable

    As per your question,first we have to convert with numeric with using case,which satisfies your first condition,another thing if the value is String than convert as NULL. In Above query both the condition has been taken care.

    EDIT : If you are using SQL SERVER 2012 or higher version then use TRY_PARSE(), then there will be no need to worry about using CASE too...

    I have tried this,

    SELECT TRY_PARSE('63.36' as decimal(18,2)) got result 63.36

    and

    SELECT TRY_PARSE('.' as decimal(18,2)) got result NULL