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

I'm looking for an elegant way to convert a field of type varchar, with variable data in it, to a data type which can be used for mathematic开发者_如何学Pythonal operations sample data from the field (excluding quotes)

''
'abc'
'23'
'23.2'

The method should work for all, and for the first & second values should return 0, and not throw an SQL Server error..

Try this:

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

You have to adjust the destination data type, I have chosen decimal(18, 2) for demonstration.

I know this is a long-dead thread, but I recently stumbled upon it from a Google search and had a thought. It is less elegant than a 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 top version would see "2 5" as just 2, the bottom one would see it as a text field.

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

If you'd like to convert it, you should use UPDATE instead of SELECT

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

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 that initially does not evaluate to NULL.

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

NULLIF essentially finds the value you specify and if it matches it replaces it with a NULL value.

CAST Simply changes a data type to another in this example to MONEY

As you can see, if you break the below down using this information its quite an elegant solution I think?

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