How to replace multiple strings in SQL?

Question

I looked up a discussion in SO with the link below : Replace multiple characters in SQL

What I am not able to understand is why the query is not working for :

select table_value, 
   replace(replace(replace(table_value, 'M', 'MEETING'), 'E', 'EMAIL'), 'P', 'PHONE') required_value 
from foobar

I am a beginner learner in SQL and want to understand what's wrong with the above query ? I'd appreciate if someone could help me picturise the backend working here.

The accepted answer in that blog is not the ultimate solution. I wanna know what to do when I come across a situation where there are multiple strings in a column which needs to be replaced?

Is there an easy and efficient alternative or not?

Thanks in advance

Edit your question and show the values in table_value and what you want them to be. — Gordon Linoff
– Gordon Linoff, Commented Aug 11, 2017 at 19:06

HLGEM · Accepted Answer · 2017-08-11 19:30:23Z

2

The code you posted will work, it just won't give correct results.

take this example data:

create table #foobar (table_value varchar (100))

Insert into #foobar
values ('M,E'), ('M,E,P'), ('E'), ('M,P')
Insert into #foobar
values ('P,E')

When you run:

select table_value, 
   replace(replace(replace(table_value, 'M', 'MEETING'), 'E', 'EMAIL'), 'P', 'PHONE') required_value 
from #foobar

You get these results

table_value required_value
M,E          MEMAILEMAILTING,EMAIL
M,E,P        MEMAILEMAILTING,EMAIL,PHONE
E            EMAIL
M,P          MEMAILEMAILTING,PHONE
P,E          PHONE,EMAIL

As you can see the problem is that in replacing m with meeting, you now have two new 'E' values in the text that will in turn get replaced with email.

So if you want to fix this the first and best solution is to stop storing data this way. Comma delimited lists in a field are almost always a bad idea. It violates the first normal form. You should have a related table where each type in the list is a separate record attached to the same id value from the original table. This is database 101.

However at times, you may be stuck with this, that's why the link wants to split the data out into a table and then process from there, then join it back into one field after updating if you need to retain the current structure and aren't just processing data in a staging table. Or alternatively not use values that will mingle as these do.

answered Aug 11, 2017 at 19:30

HLGEM

97k15 gold badges120 silver badges191 bronze badges

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

3 Comments

shuberman Over a year ago

Yeah but assuming I'm stuck with a scenario as above ...So is there any other simple alternative ? Or I have to go with nesting of REPLACE ?

HLGEM Over a year ago

There are no simple fixes because your data model is poorly designed. The link has the actual fixes, use those.

shuberman Over a year ago

Thank you so much. I couldn't have figured it without your clear explanation.Keep up the good work :)

Juan Carlos Oropeza · Accepted Answer · 2017-08-11 19:34:13Z

0

The problem is if you replace

M -> MEETING

and then

E -> EMAIL

you end with:

MEMAILEMAILTING

Maybe you can do a workaround if you use the comma as separator

select table_value, 
   replace(replace(replace(table_value, 'M,', 'MEETING,'), 'E,', 'EMAIL,'), 'P,', 'PHONE,') required_value 
from foobar

But then your starting data should include one ending coma:

table_value
M,E,
M,E,P,
E,  
M,P,
P,E,

edited Aug 11, 2017 at 19:34

answered Aug 11, 2017 at 19:29

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

4 Comments

shuberman Over a year ago

Yeah but isn't E supposed to be replaced with EMAIL and not MEETING? Or is the first occurence of E making it to be replaced with EMAIL?

Juan Carlos Oropeza Over a year ago

You are chaining replace, so the replace occurs to the previous result not the original string, So after you replace M -> MEETING you have EE more than before

shuberman Over a year ago

I guess I'll have to chain cleverly in such cases. Although I believe such scenarios may occur rarely unless you have a poorly designed database.

Juan Carlos Oropeza Over a year ago

As I show you, if you replace(table_value, 'M,', 'MEETING,') You can avoid the issue.

Javlon Ismatov · Accepted Answer · 2017-08-11 19:54:28Z

0

select case [replace] when 'M' then 'MEETING' 
             when 'E' then 'EMAIL'  
             when 'P' then 'PHONE'  end 'required_value' 
  from your_table

answered Aug 11, 2017 at 19:54

Javlon Ismatov

1941 silver badge12 bronze badges

Collectives™ on Stack Overflow

How to replace multiple strings in SQL?

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related