SQL: replace specific character from string

Question

How to replace only 2 with 1 from string (12 | 2) & 23 | 4 & 25 | (6 | 7). but don't want to replace 2 which are within 12,23,25..

Because i don't know, how many 2 are in string. So i only want to replace 2.

I tried with replace clause but it replaces all 2.

So problem arising while replacing only 2.

http://sqlfiddle.com/#!3/a1821/3/0 to help test. – xQbert Nov 07 '13 at 14:23 — xQbert, Nov 07 '13 at 14:23

score 5 · Answer 1 · edited May 23 '17 at 11:57

I answered a question a long time ago that may help: How to strip all non-alphabetic characters from string in SQL Server?

In that question, I show a block of code that is also helpful here (with modifications of course).

 Declare @Temp VarChar(100)
 Set @Temp = '(12 | 2) & 23 | 4 & 25 | (6 | 7)'

 Declare @FindCharacter VarChar(10),
        @ReplaceCharacter VarChar(10)

Set @FindCharacter = '2'
Set @ReplaceCharacter = '1'

 While PATINDEX('%[^0-9]' + @FindCharacter + '[^0-9]%', @Temp) > 0
    Set @Temp = Stuff(@Temp, PATINDEX('%[^0-9]' + @FindCharacter + '[^0-9]%', @Temp) + 1, Len(@FindCharacter), @ReplaceCharacter)

Select @Temp

Basically, this code searchs for {not a number}{value 2}{not a number}. This also means that the code will not replace the 2 if it is the first or last character of the string. The "replace" here is actually accomplished with the STUFF function. Since STUFF can only replace one string at a time, you need a while loop to cause it to do the replace multiple times.

Here is a scalar UDF to do the replacement. It will accommodate replacements even if they are at the beginning or end of the search string.

Create Function dbo.ReplaceNumber(@Input VarChar(100), @Find VarChar(10), @Replace VarChar(10))
Returns VarChar(100)
AS
Begin

    Set @Input = '~' + @Input + '~'

    While PATINDEX('%[^0-9]' + @Find + '[^0-9]%', @Input) > 0
        Set @Input = Stuff(@Input, PATINDEX('%[^0-9]' + @Find + '[^0-9]%', @Input) + 1, Len(@Find), @Replace)

    Return SubString(@Input, 2, Len(@Input)-2)
End

You can test/use the function like this:

Select dbo.ReplaceNumber('(12 | 2) & 23 | 4 & 25 | (6 | 7)','2','1')
Select dbo.ReplaceNumber('2 & 23','2','1')
Select dbo.ReplaceNumber('2 & 23','23','10')

Instead of '2' if i have to replace '1' character from string, where need to do changes into your answer?? — Parth Patel, Nov 07 '13 at 14:42
If i have to replace first and last character then what to do?? — Parth Patel, Nov 07 '13 at 14:52
I updated my answer with a slightly more generalized solution. This will still not accommodate if the search string is the first or last character. — George Mastros, Nov 07 '13 at 15:47
I added to my answer. I included a user defined function that you can add to your database that will do the replace for you. — George Mastros, Nov 07 '13 at 16:24
certainly, it's a useful fascinating while loop must be collected!+1;) — Aleksandr Fedorenko, Nov 08 '13 at 08:29

Aleksandr Fedorenko · Accepted Answer · 2013-11-07T22:34:46.143

The following example compares the previous, current and next values at each step of the recursive CTE

DECLARE @text nvarchar(max) = '(12 | 2) & 23 | 4 & 25 | (6 | 7)',
        @Search nvarchar(1) = '2',
        @Repl nvarchar(1) = '1'
;WITH cte AS
  (SELECT 1 AS Number, CASE WHEN SUBSTRING (@text, 1, 1) = @Search
                            THEN @Repl ELSE SUBSTRING (@text, 1, 1) END AS Num
   UNION ALL
   SELECT c.Number + 1,
      c.Num + CASE WHEN SUBSTRING (@text, c.Number + 1, 1) = @Search
                    AND ISNUMERIC(SUBSTRING(@text, c.Number, 1)) = 0
                    AND ISNUMERIC(SUBSTRING(@text, c.Number + 2, 1)) = 0
                       THEN @Repl ELSE SUBSTRING (@text, c. Number + 1, 1) END
   FROM cte c
   WHERE Number < LEN(@text)
   )
   SELECT TOP 1 Num
   FROM cte
   ORDER BY Number DESC

Demo on SQLFiddle

SQL: replace specific character from string

2 Answers2