I have a requirement to move varchar column data to Numeric but with two conditions.
- All the alphanumeric value should migrate as null
- 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
6条答案
按热度按时间mzmfm0qo1#
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,
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 usingCASE
too...I have tried this,
and
ztyzrc3y2#
I think that this fits your spec. It is quite verbose, but hopefully it breaks down the conditions sufficiently that it's clearly doing the correct thing or, if it isn't, that it's easy enough to modify:
Results:
I.e. one additional check you may want to use is that a decimal cannot be the first or last character in the string. That is easy enough to do by adding those additional checks into the first
CASE
for theSingleDec
column.b1payxdu3#
try ISNUMERIC function,
xxls0lw84#
On SQL Server (Version 2012, 11.0.5343)
works fine ...
ldfqzlk85#
Thre is A blog post .
Try following
ix0qys7i6#
try ISNUMERIC function
Output