July 8, 2022 at 8:26 am
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.
July 8, 2022 at 8:44 am
Not to worry, I'm interested in the answer but I guess I can do this with charindex ... I'll give it a go.
July 8, 2022 at 8:45 am
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
July 8, 2022 at 12:56 pm
Great, thanks! That does the trick perfectly.
July 8, 2022 at 8:09 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply