Clever Geek Handbook

SQL Server converting variable field varchar to money / decimal / something with decimal places

I'm looking for an elegant way to convert a varchar field with variable data to it to a data type that can be used to sample math operations data from the field (excluding quotes)

''
'abc'
'23'
'23.2'

The method should work for everyone, and for the first and second values, it should return 0 and not throw a SQL Server error.

+2


source to share


5 answers


Try the following:

SELECT CASE WHEN IsNumeric(YourColumn) = 0 THEN 
           0 
       ELSE 
           CAST(YourColumn AS decimal(18, 2)) 
       END


You have to customize the data type of the destination, I chose decimal (18, 2) for demonstration.

+3


source


I know this is a long dead thread, but I recently stumbled upon it from a google search and thought. It is less elegant than the CASE statement, but it is an alternative.

SELECT
    COALESCE(CAST(NULLIF(ISNUMERIC(LEFT(MyColumn, PATINDEX('% %', MyColumn + ' ') - 1)), 1) AS MONEY), LEFT(MyColumn, PATINDEX('% %', MyColumn + ' ') - 1))
FROM
    myTable

or you could do:



Select COALESCE(CAST(NULLIF(ISNUMERIC(MyColumn), 1) AS MONEY), MyColumn)
FROM
    myTable

The upper version will see "2 5" as soon as 2, the lower one will see it as a text box.

+1


source 


SELECT  CASE IsNumeric(mycol) WHEN 1 THEN CAST(mycol AS FLOAT) ELSE 0 END
FROM    mytable
0


source


If you want to convert it you should use UPDATE

insteadSELECT 

UPDATE Table
SET Col1 = CAST(Col1 As Decimal(18,2))
0


source


COALESCE

is a great option for this: Find more information here. It evaluates the arguments in order and returns the current value of the first expression, which does not initially evaluate to NULL.

ISNUMERIC

returns 0 or 1 depending on whether the value being evaluated can be considered one of the SQL types 'number' or 'numeric'. e.g. int, bigint, money ..

NULLIF

essentially finds the value you specified and, if it matches, replaces it with the value NULL

.

CAST

Will just change the datatype to something else in this example to MONEY

As you can see, if you break the bottom down using this information, it's a pretty elegant solution I guess?

COALESCE(CAST(NULLIF(ISNUMERIC(COL1), 1) AS MONEY), COL1)
0


source






More articles:


All Articles