Replace part of string in SQL

Question

I got thousand of strings that contains data and with id's that is marked with a '@xx'. If the string has a '|' it menas that there is more data with an ID.

How do I find and replace all ID's that contains a specific ID? If I want to change @1 to @24 I don't want @11 to be changed to @241

Some exampels with expected output:

21600-39600@1 -> 21600-39600@24 
21600-39600@2 -> 21600-39600@2
21600-39600@7|39600-52200@11|68000-72200@1 -> 21600-39600@7|39600-52200@11|68000-72200@24

Replace(column, '@1', '@24') will not work because @11 will be changed to @241. So I need to know if it is the end of the string or if it ends with an '|'

show your expected output

TheGameiswar
– TheGameiswar

2016-12-08 09:18:37 +00:00
Commented Dec 8, 2016 at 9:18 — TheGameiswar
– TheGameiswar, Commented Dec 8, 2016 at 9:18
I did edit it with expected output

thatsIT
– thatsIT

2016-12-08 09:45:31 +00:00
Commented Dec 8, 2016 at 9:45 — thatsIT
– thatsIT, Commented Dec 8, 2016 at 9:45

Jens · Accepted Answer · 2016-12-08 09:25:04Z

2

Quick and dirty but you could just do something like:

-- Update the end bits
UPDATE table
SET field = LEFT('field', LEN('field') -2) + '@24'
WHERE field LIKE '%@1';

-- Update the in between bits
UPDATE table
SET field = REPLACE(field, '@1|', '@24|')
WHERE field LIKE '%@1|%'; -- This where statement is optional due to the nature of the REPLACE.

Otherwise you'll have to look into the magical world of REGEX. If this is something you'd want to run more than once, I'd surely look into that. If this is just a one-time-fix-thingy, meh. It's thursday, I'd call it a valid excuse.

edited Dec 8, 2016 at 9:25

answered Dec 8, 2016 at 9:17

Jens

3,3092 gold badges28 silver badges43 bronze badges

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

Comments

Madhivanan · Accepted Answer · 2016-12-08 10:13:24Z

1

Try this

declare @t table(col varchar(100))
insert into @t 
select '21600-39600@1' union all
select '21600-39600@2' union all
select '21600-39600@7|39600-52200@11|68000-72200@1'


select col,case when new_col like '%|' then 
               substring(new_col,1,len(new_col)-1) 
            else 
                 new_col end as new_col 
from
(
select col,
case when col+'|' like '%@1|%' then 
Replace(col+'|', '@1|', '@24|') else col end as new_col 
from @t
) as t

Result

col                                            new_col
---------------------------------------------  ------------
21600-39600@1                                 21600-39600@24
21600-39600@2                                 21600-39600@2
21600-39600@7|39600-52200@11|68000-72200@1    21600-39600@7|39600-52200@11|68000-72200@24

edited Dec 8, 2016 at 10:13

answered Dec 8, 2016 at 9:13

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

4 Comments

3N1GM4 Over a year ago

What about the strings with multiple | delimited sections, each with their own @xx value? Or was that edit added after you answered?

Jens Over a year ago

What happens when one of the middle ones contains @1| ? Wouldn't that result in stripping the | in the middle as well?

Jens Over a year ago

People get too focussed on writing everything in a single query these days, while spliting stuff like this up enhances readability massively.

Madhivanan Over a year ago

Yes I agree. But I do not think you need UPDATE in this case as you need to keep on updating the data when new data are added. So better way is to create a view with just SELECT

Collectives™ on Stack Overflow

Replace part of string in SQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related