2

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 mathematical 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..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pablo
  • 761
  • 4
  • 8
  • 14
  • Often when you have data of different types stored in a field, it indicates that you have a database design problem. – HLGEM Jul 19 '12 at 18:53

5 Answers5

4

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.

Maximilian Mayerl
  • 11,253
  • 2
  • 33
  • 40
  • 2
    IsNumeric is a nasty little function, as long as a type converts to any of the numeric types it returns 1, but you do not know which type is has agreed it can convert to, so for the input '£1.20' it will throw an error on the cast, even though it passed the isnumeric. – Andrew Oct 13 '09 at 10:59
  • 1
    It is incredibly difficult to £1.2 will convert to money, which is why the IsNumeric allows it, but refuses to convert to anything else. '1e1' is numeric because it can will convert to a real or float, but not to an int, even thought the result is a whole number. I've never seen a compelling solution, it's already been covered in http://stackoverflow.com/questions/312054/efficient-isnumeric-replacements-on-sql-server for examples of workarounds, all with pros and cons. – Andrew Oct 13 '09 at 13:05
  • "Cannot convert a char value to money" – Dainius Kreivys Jan 25 '16 at 13:13
1

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.

RPh_Coder
  • 833
  • 8
  • 15
0

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

UPDATE Table
SET Col1 = CAST(Col1 As Decimal(18,2))
Simon Dugré
  • 17,980
  • 11
  • 57
  • 73
dp3
  • 1,607
  • 4
  • 19
  • 28
0
SELECT  CASE IsNumeric(mycol) WHEN 1 THEN CAST(mycol AS FLOAT) ELSE 0 END
FROM    mytable
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

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)
JeffreyShran
  • 77
  • 11