sql server - How to Replace all occurrence of a character except last in a string in SSIS?

Question

I have a string number like this:

1.000.000.00

So I have to delete all dots except last one, it will look like this:

1000000.00

Also if there is only one dot it should keep it.

Example:

INPUT            |       OUTPUT
-----------------------------------
1.00             |       1.00
1.000.00         |       1000.00

What I tried:

I create a derived column with this expression

LEFT([Column],FINDSTRING([Column],".",LEN([Column]) - LEN(REPLACE([Column],".",""))) - 1) 
+ SUBSTRING([Column],FINDSTRING([Column],".",LEN([Column]) - LEN(REPLACE([Column],".",""))) + 1,LEN([Column]))

It keep the first dot not the last one.

question from:https://stackoverflow.com/questions/65893761/how-to-replace-all-occurrence-of-a-character-except-last-in-a-string-in-ssis

Answer 1

You can try to use SSIS TOKEN() and TOKENCOUNT() functions.

TOKENCOUNT(column,".") == 4 ? TOKEN(1) + TOKEN(2) + TOKEN(3) + "." + TOKEN(4) : 
( TOKENCOUNT(column,".") == 3 ? TOKEN(1) + TOKEN(2) + "." + TOKEN(3) : 
( TOKENCOUNT(column,".") == 2 ? column : 
( TOKENCOUNT(column,".") == 0 ? column: “"  ) ) )