SQL Server How to convert Varchar column to Numeric

3vpjnl9f  于 2023-10-15  发布在  其他
关注(0)|答案(6)|浏览(127)

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
mzmfm0qo

mzmfm0qo1#

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
ztyzrc3y

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:

declare @t table (data varchar(30))

insert into @t(data) values
('1234'),
('1.23'),
('abc'),
('.abc'),
('+6000'),
('1.2.3')

select
    CASE WHEN
        Possible = 1 AND
            (DecCheck = 0 OR
            SingleDec = 1
        ) THEN
            CONVERT(decimal(12,3),data)
        END
from
    @t t
        cross apply
            (select
                --Only contains the correct characters
                CASE WHEN not t.data like '%[^0-9.]%' THEN 1 ELSE 0 END as Possible,
                --Contains a decimal point? (Needs more checks)
                CASE WHEN CHARINDEX('.',t.data) > 0 THEN 1 ELSE 0 END as DecCheck,
                CHARINDEX('.',t.data) as FirstDec --Where the first decimal point is
            ) p
        cross apply
            (select
                CASE WHEN DecCheck = 1 THEN
                    --Only contains one decimal point
                    CASE WHEN LEN(data) = FirstDec + CHARINDEX('.',REVERSE(data)) - 1
                        THEN 1
                    ELSE 0 END
                ELSE 0 END as SingleDec
            ) d

Results:

data                           
------------------------------ ---------------------------------------
1234                           1234.000
1.23                           1.230
abc                            NULL
.abc                           NULL
+6000                          NULL
1.2.3                          NULL

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 the SingleDec column.

b1payxdu

b1payxdu3#

try ISNUMERIC function,

SELECT ISNUMERIC('abc')
SELECT ISNUMERIC('1.23')
xxls0lw8

xxls0lw84#

On SQL Server (Version 2012, 11.0.5343)

SELECT
    CASE WHEN ISNUMERIC('.') = 1 THEN <Field> ELSE 0 END
FROM
    <Table>

works fine ...

ldfqzlk8

ldfqzlk85#

Thre is A blog post .

Try following

SELECT 
    CASE 
        WHEN 
            ISNUMERIC(data + 'e0') = 1 THEN CAST(data AS decimal(18,2))
        ELSE NULL END AS tData 
FROM 
    DataTable
ix0qys7i

ix0qys7i6#

try ISNUMERIC function

DECLARE @MyTable TABLE(Val VARCHAR(100))

INSERT INTO @MyTable
VALUES
     ('1234')
    ,('1.23')
    ,('ABC')
    ,('.ABC')
    ,('MJA')

Select  Val as OldValue,
        Case
        When ISNUMERIC(Val) = 1
        then Cast(Val as numeric(18,2))
        else null
        end NewValue
From    @MyTable

Output

OldValue                                  NewValue
-----------------------------------------------------
  1234                                      1234.00
  1.23                                      1.23
  ABC                                       NULL
  .ABC                                      NULL
  MJA                                       NULL

  (5 row(s) affected)

相关问题