0

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

shuberman
  • 1,416
  • 6
  • 21
  • 38

3 Answers3

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 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 ? – shuberman Aug 11 '17 at 19:36
  • There are no simple fixes because your data model is poorly designed. The link has the actual fixes, use those. – HLGEM Aug 11 '17 at 19:37
  • Thank you so much. I couldn't have figured it without your clear explanation.Keep up the good work :) – shuberman Aug 11 '17 at 19:40
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,
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 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? – shuberman Aug 11 '17 at 19:33
  • 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 – Juan Carlos Oropeza Aug 11 '17 at 19:35
  • 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. – shuberman Aug 11 '17 at 19:42
  • As I show you, if you `replace(table_value, 'M,', 'MEETING,')` You can avoid the issue. – Juan Carlos Oropeza Aug 11 '17 at 19:44
0
select case [replace] when 'M' then 'MEETING' 
             when 'E' then 'EMAIL'  
             when 'P' then 'PHONE'  end 'required_value' 
  from your_table
Javlon Ismatov
  • 194
  • 12