Find a word of a column in another column and replace?

Question

this is the first time I'm posting here! Hope to get a good reply.

I have two table, and based on the path I need to locate and replace with '' the name of table2 in the name of table1.

table1
+---------------+----------+
| name          | path     |
+---------------+----------+
| John Smith    | 12345    |
+---------------+----------+
| John Smith    | 54321    |
+---------------+----------+
| JohnSmith     | 12345    |
+---------------+----------+

table2
+---------------+----------+
| name          | path     |
+---------------+----------+
| John          | 12345    |
+---------------+----------+
| Smith         | 54321    |
+---------------+----------+

the final result would be like
+---------------+----------+
| name          | path     |
+---------------+----------+
| Smith         | 12345    |
+---------------+----------+
| John          | 54321    |
+---------------+----------+
| JohnSmith     | 12345    |
+---------------+----------+

as you can see I need to replace only the exact match. So in JohnSmith I don't remove John.

The question is also, do I do it only with a mysql query? Or some php is also needed for something like that?

Thank you in advance.

both queries are close, but don't give exactly what I need as an output.

e.g. the first one produce

NAME        PATH
Smith       12345
John        54321
Smith       12345

here the last row, should not be changes. As we don't have any exact match. and the row should stay the same "JohnSmith"

in the second one, the output is

NAME        PATH
Smith       12345
John Smith  54321
JohnSmith   12345

here the second line seems wrong, as it should remove "Smith"

any idea ?

http://stackoverflow.com/q/986826/1291428 – Sebas Feb 17 '13 at 14:53 — Sebas, Feb 17 '13 at 14:53

sgeddes · Accepted Answer · 2013-02-17T15:09:12.347

0

This is close, but I admittedly don't understand your last condition -- shouldn't it be Smith/12345? This uses REPLACE:

SELECT REPLACE(t1.Name, t2.Name, '') Name, t1.Path
FROM Table1 t1
  LEFT JOIN Table2 t2 ON t1.path = t2.path

And here is the SQL Fiddle.

--EDIT--

Here is an attempt using a CASE statement. It checks for in the middle, in the beginning, or at the end:

SELECT 
  CASE 
    WHEN t1.Name Like CONCAT('% ',IFNULL(t2.Name,''),' %')
    THEN REPLACE(t1.Name, CONCAT(' ',IFNULL(t2.Name,''),' '), ' ') 

    WHEN t1.Name Like CONCAT(IFNULL(t2.Name,''),' %')
    THEN REPLACE(t1.Name, CONCAT(IFNULL(t2.Name,''),' '), '') 

    WHEN t1.Name Like CONCAT('% ',IFNULL(t2.Name,''))
    THEN REPLACE(t1.Name, CONCAT(' ',IFNULL(t2.Name,''),' '), '') 

    ELSE
       t1.Name

END Name, t1.Path
FROM Table1 t1
  LEFT JOIN Table2 t2 ON t1.path = t2.path

And more Fiddle.

Produces the following results:

NAME        PATH
Smith       12345
John Smith  54321
JohnSmith   12345

edited Feb 17 '13 at 15:09

answered Feb 17 '13 at 14:57

sgeddes

62,311
6
61
83

I put my reply as answer... sorry for that :) both queries are close... but not exactly what I need as an output... – user2080591 Feb 18 '13 at 07:44
@user2080591 -- had an extra space in there that wasn't getting removed -- here you go: http://www.sqlfiddle.com/#!2/c6cba0/17 – sgeddes Feb 18 '13 at 12:33
here is a problem. in table2, I might have many rows with the same path. they are not duplicate, as they are different spelling variations. How do I change the query, not to be select but update and not to show duplicates, as now with the select and multiple rows in table2 the left join show 10 times the same row... or, how to use the CASE query to do updates in table1 ? thank you – user2080591 Feb 19 '13 at 07:33

score 0 · Answer 2 · edited Feb 18 '13 at 11:53

0

This will do the job:

SELECT 
    REPLACE(CONCAT(' ',t1.Name,' '), 
    CONCAT(' ',t2.Name,' '), '') Name, 
    t1.Path
FROM 
    Table1 t1
LEFT JOIN 
    Table2 t2 ON t1.path = t2.path

edited Feb 18 '13 at 11:53

Spiny Norman

8,277
1
30
55

answered Feb 18 '13 at 11:32

user2080591

3
2

here is a problem. in table2, I might have many rows with the same path. they are not duplicate, as they are different spelling variations. How do I change the query, not to be select but update and not to show duplicates, as now with the select and multiple rows in table2 the left join show 10 times the same row... thank you – user2080591 Feb 19 '13 at 07:28

Find a word of a column in another column and replace?

2 Answers2