-2

I have a data table where I have numbers expressed in the following format:

Incorrect  | What I want to format it to
------------------------------------------
123,452,03 | 123,452.03
234.00     | 234.00
456,02     | 456.02

The challenge that I have is some numbers have more than one comma. I know this can be solved via regex, but can use some help in constructing the string. The logic should be as following:

  1. Check if string has comma
  2. If after the comma there are two numbers and they are at the end of the string
  3. Replace the comma value with a period
  4. If after the comma there are three numeric values, or if is in the middle of the string, do nothing.

EDIT: Using Sql Server to construct the expression.

Raptor776
  • 190
  • 1
  • 5
  • 15
  • Which dbms are you using? (The regex functionality can be quite different, depending on product used.) – jarlh Nov 11 '21 at 22:31
  • Using sql server :) – Raptor776 Nov 11 '21 at 22:32
  • 4
    It's a bad idea to store numeric data as strings. Use proper column data type, and you will not have this problem. – jarlh Nov 11 '21 at 22:34
  • Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Nov 11 '21 at 23:29

1 Answers1

1

What a bad idea to store numbers in string columns. And then even store invalid numbers. Well, check the antepenultimate character. If it's a comma change it to a dot.

case when left(right(str, 3), 1) = ',' then
  stuff(str, len(str) - 2, 1, '.')
else
  str
end

Or if all numbers end with two decimals just:

stuff(str, len(str) - 2, 1, '.')

(Once you've repaired your numbers, you should put them in a numeric column and drop the existing text column.)

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • This works, thank you! Sadly, not my data, it is a data feed we get from a very antiquated system. I will just modify the query to convert it into a numeric column. – Raptor776 Nov 11 '21 at 22:54