Ankit Ankit - 1 year ago 116
SQL Question

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

Answer Source

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


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 than use TRY_PARSE(), than there will be no need of worry about using CASE too...

I have tried this,

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


SELECT TRY_PARSE('.' as decimal(18,2)) got result NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download