Change commas to decimals and remove extra spaces in field

[#1]

Hi I have a column in a excel spreadsheet that has number like these below:

0.96.0.1.2

10, 00, 9061, 89

12, 32, 11282, 0

11.20.8231.9

10, 21, 89347, 0

11.2 HR8

11.2.12459.0

NULL

How can I get rid of the extra spaces and replace the commas with decimals? The correct output should be display like below:

0.96.0.1.2

10.00.9061.89

12.32.11282.0

11.20.8231.9

10.21.89347.0

11.2 HR8

11.2.12459.0

NULL

[trdandamudi]

Please see the attached and hope this helps...

[swuehl]

Maybe just use Replace() function:

LOAD *,

  Replace(Test,', ','.') as Test2

INLINE [

Test

"0.96.0.1.2"

"10, 00, 9061, 89"

"12, 32, 11282, 0"

"1.20.8231.9"

"10, 21, 89347, 0"

"11.2 HR8"

"11.2.12459.0"

"NULL"

];

[#4]

The column, Versions, has more numbers listed. The numbers I listed in my original post was just some of them. How do I change all of them? Would I have to type each number out in the Inline bracket?

[sunny_talwar · Accepted solution]

This can be done from your actual source like this....

LOAD ListOfFields,

          Number,

          Replace(Test,', ','.') as NewNumber

FROM Source....

[swuehl]

Right, the INLINE table is only used for demonstration, just use the Replace() function in your LOAD statement that you are using to load your table from source.