-3

I'm looking for tips on putting together a user defined function to convert a varchar to numeric. The trick is i'm after one that converts common text representations of strings to numeric. Also, if it can't be parsed as a numeric, i'd like it to return NULL rather than throwing an error and stopping the query.

Examples:

  • varchar '10%' should parse as numeric 0.1 (No remove % sign and divide by 100)
  • varchar '$1,00.1234' should parse as numeric 100.1234 (remove $ sign and ,)
  • varchar '$1k' should parse as numeric 1000 (k converts to multiply by 1 thousand)
  • varchar '$1M' should parse as numeric 1000000 (m converts to multiply by 1 Million)
  • varchar '$1B' should parse as numeric 1000000000 (b converts to multiply by 1 Billion)
  • varchar '100 ' should parse as numeric 1000 (LTrim & RTrim)
  • varchar 'Random Text' should parse as numeric NULL (Null won't thrown an error)
Bridge
  • 29,818
  • 9
  • 60
  • 82
user1788158
  • 29
  • 1
  • 4

1 Answers1

2

I'd suggest you use a CLR function to parse to this complexity. Then fix the data

You have a mix of currency, percentages, mixed decimal/separator formatting, in addition to non-numeric values.

gbn
  • 422,506
  • 82
  • 585
  • 676