SQL replace with escape characters

Question

Post reply

SQL replace with escape characters

snomadj

SSCarpal Tunnel

Points: 4491
More actions
July 8, 2022 at 8:26 am

#4060519

I want to do
UPDATE x
SET y = (REPLACE(y,'a=[123]', a=999)
FROM x
So I'm replacing a=(any 3 integers) with a=999.
I'm so stuck on this. Any help, gratefully received.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 1

Not to worry, I'm interested in the answer but I guess I can do this with charindex ... I'll give it a go.

DesNorton SSC-Insane Points: 24387 More actions · Answer 2

You can use PATINDEX to find the a=(any 3 integers), then use STUFF to replace the (any 3 integers) with 999

DECLARE @x table (y varchar(50));

INSERT INTO @x (y)
VALUES ( 'a=12' )
     , ( 'a=123' )
     , ( 'a=123 b=567' )
     , ( 'b=567 a=123' )
     , ( 'a=1234567' )
     , ( 'a=1234567 b=567' )
     , ( 'b=567 a=1234567' )


UPDATE @x
SET y = STUFF(y, PATINDEX('%a=[0-9][0-9][0-9]%', y) +2, 3, '999')
-- SELECT y, NewValue = STUFF(y, PATINDEX('%a=[0-9][0-9][0-9]%', y) +2, 3, '999')
FROM  @x
WHERE PATINDEX('%a=[0-9][0-9][0-9]%', y) > 0

y                    NewValue
-------------------- ----------------
a=123                a=999
a=123 b=567          a=999 b=567
b=567 a=123          b=567 a=999
a=1234567            a=9994567
a=1234567 b=567      a=9994567 b=567
b=567 a=1234567      b=567 a=9994567

How to post data/code on a forum to get the best help.

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 3

snomadj

SSCarpal Tunnel

Points: 4491

July 8, 2022 at 12:56 pm

#4060633

Great, thanks! That does the trick perfectly.

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 4

snomadj wrote:

Great, thanks! That does the trick perfectly.

Be careful... it only works for a single instance on any one row. If there are two instances, it only takes care of the first. For example, if on of the rows contains , ( 'a=1231 b=1232'), it's only going to convert the first instance of '123' to '999'. The second instance will remain as it is.

If that's what you want, then you're golden. If it's not, then come back and say that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)