CODEWITHSUNDEEP
mysql
G-M
G-M

Reputation: 346

Translation Table, Replace String and Select proper Row

I have two tables, one with strings, that need to be checked for certain strings that need to be replaced. The second table contains two columns, one with strings to be searched and one with the strings to be replaced - to be short a translation table:

|Name     |Age|           |Short|Long  |
---------------           ------------
|Mr. Smith|23 |           |Mr.  |Mister|
|Ms. Anna |78 |           |Ms.  |Misses|
|Jack Jr. |12 |           |Jr.  |Junior|
|Mister A |23 |           --------------
|John     |41 |
---------------

I think I have to do a Cross-Join and select distinct values.

But now I have both rows, the ones with Mr. and Mister, how do I select only the ones with "Mister":

[I need this table]             [but this is the result]
|Name         |Age|             |Name         |Age|     
-------------------             -------------------     
|Mister Smith |23 |             |Mr. Smith    |23 |     
|Misses Anna  |78 |             |Mister Smith |23 |
|Jack Junior  |12 |             |Ms. Anna     |78 | 
|Mister A     |23 |             |Misses Anna  |78 | 
|John         |41 |             |Jack Jr.     |12 | 
-------------------             |Jack Junior  |12 | 
                                |Mister A     |23 |
                                |John         |41 | 
                                -------------------

I can't do an "Except" because the table contains also rows, that don't need to be translated.

Thanks in advance.

Upvotes: 0

Views: 112

Answers (1)

seahawk
seahawk

Reputation: 1912

Use following sql query:

select ifnull(replace(t1.name,t2.short_name,t2.long_name),t1.name) as name,
t1.age

from
t1
left join t2
on t2.short_name=if(instr(t1.name,t2.short_name)>0,t2.short_name,t1.name)

Note: Use UPPER or LOWER function for case-mismatch. Use TRIM function for space issue.

Verify result at http://sqlfiddle.com/#!9/6b238/5

Upvotes: 1

Related Questions