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.

sqlfiddle.com/#!3/a1821/3/0 to help test.

xQbert
– xQbert

2013-11-07 14:23:47 +00:00
Commented Nov 7, 2013 at 14:23 — xQbert
– xQbert, Commented Nov 7, 2013 at 14:23

Community · Accepted Answer · 2017-05-23 11:57:31Z

5

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

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Nov 7, 2013 at 14:33

George Mastros

24.6k5 gold badges56 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Parth Patel Over a year ago

Instead of '2' if i have to replace '1' character from string, where need to do changes into your answer??

Parth Patel Over a year ago

If i have to replace first and last character then what to do??

George Mastros Over a year ago

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 Over a year ago

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.

Oleksandr Fedorenko Over a year ago

certainly, it's a useful fascinating while loop must be collected!+1;)

Oleksandr Fedorenko · Accepted Answer · 2013-11-07 22:34:46Z

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

Collectives™ on Stack Overflow

SQL: replace specific character from string

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related