I have a requirement to move varchar column data to Numeric but with two conditions.
WHERE data like '%[^0-9]%'
1) 1234 as 1234
2) 1.23 as 1.23
3) ABC as null
4) .ABC as null
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