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.

CodePudding user response：

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