Replacing specific char in a string

Question

I'm trying to do a SQL replace when there's a count of two or more of a specific char, but the kicker is that I need to replace all except the last one.

So, let's say I have this string 1.235.36 and I'm trying to remove the first decimal and leave the last one.

I want to turn 1.235.36 to 1235.36.

I have the count method here but I'm having trouble thinking a way to replace it without replace all of the decimal.

declare @myvar varchar(20)
set @myvar = '1.234.54'

select len(@myvar) - len(replace(@myvar,'.',''))

Update: I do not want to replace all, but keep last one. I'm using SQL Server

related: https://stackoverflow.com/questions/38911588/replace-first-occurrence-of-substring-in-a-string-in-sql — dustytrash, Sep 11 '18 at 18:48
Possible duplicate of [Replace first occurrence of substring in a string in SQL](https://stackoverflow.com/questions/38911588/replace-first-occurrence-of-substring-in-a-string-in-sql) — dustytrash, Sep 11 '18 at 18:49
@dustytrash, OP wants to replace all occurrences except the last. It's not strictly a duplicate of the question you linked. — Ryan B., Sep 11 '18 at 19:04

score 1 · Accepted Answer · answered Sep 11 '18 at 19:26

If you are using SQL-Server, then you may try this query:

SELECT *, 
       replace( left(x,len(x) - charindex( '.', reverse(x))),'.','')
       + substring(x, len(x) - charindex( '.', reverse(x))+1,len(x)) As y
FROM ttt

Demo: http://www.sqlfiddle.com/#!18/e26d0/2

|          x |         y |
|------------|-----------|
| 111.234.54 | 111234.54 |
|   1.234.54 |   1234.54 |
|    1234.54 |   1234.54 |
|    1234x54 |   1234x54 |
|            |           |
|    ....... |         . |

score 0 · Answer 2 · answered Sep 11 '18 at 19:28

You could do this using reverse, charindex and substring functions. It will remove all dots except decimal separator.

select reverse(replace(reversedWholeNumber, '.', '')) + reverse(reversedDecimal)
from (
    select substring(myvar, 1, charindex('.', myvar)) reversedDecimal, 
           substring(myvar, charindex('.', myvar) + 1, len(myvar)) reversedWholeNumber
    from (
        select reverse(@myvar) myvar
    ) a
) a

or even shorter version:

select reverse(replace(substring(myvar, charindex('.', myvar) + 1, len(myvar)), '.', ''))  +
       reverse(substring(myvar, 1, charindex('.', myvar)))
from (
    select reverse(@myvar) myvar
) a

score 0 · Answer 3 · answered Sep 11 '18 at 20:03

Another way, if you grab a copy of NGrams8K you can do this:

DECLARE @string VARCHAR(1000) = '1.235.36';

SELECT COALESCE(
         REPLACE(SUBSTRING(@string, 1, MAX(ng.position)),'.','') + 
         SUBSTRING(@string, MAX(ng.position), 8000),@string)
FROM dbo.ngrams8k(@string,1) AS ng
WHERE ng.token = '.'
AND LEN(@string) - LEN(REPLACE(@string,'.','')) > 1;

Against a table it would look like this:

DECLARE @table TABLE (string VARCHAR(1000));
INSERT @table(string)
VALUES('123.123.123'),('999.00'),('12345');

SELECT t.string, f.newstring 
FROM @table AS t
CROSS APPLY 
(
  SELECT COALESCE(
           REPLACE(SUBSTRING(t.string, 1, MAX(ng.position)),'.','') + 
           SUBSTRING(t.string, MAX(ng.position), 8000),t.string)
  FROM dbo.ngrams8k(t.string,1) AS ng
  WHERE ng.token = '.'
  AND LEN(t.string) - LEN(REPLACE(t.string,'.','')) > 1
) AS f(newstring)

Results:

string       newstring
------------ -------------
123.123.123  123123.123
999.00       999.00
12345        12345

Replacing specific char in a string

3 Answers3