shingala sohil shingala sohil - 7 months ago 6
SQL Question

Mysql query match exact word and remove that exact word from database

I am using self join query here with case when statement. my query should work like it matches whole exact word from database not should match word within word.

After i finding exact word match it should remove that word from that string and update database.

When i match it can match exact word but problem is it update word within word. please see my query here.

update table1 a
left join table1 b on
(a.id = b.id)
SET a.column = CASE
WHEN b.column2 IS NOT NULL THEN REPLACE(a.column, 'ich', '')
END
where (a.column REGEXP '[[:<:]]ich[[:>:]]')


Original string(column) :

ich fühlte mich beobachtet, konnte nicht in ruhe auswählen


above query output string(column) :

fühlte m beobachtet,  nt  ruhe auswählen


output must come(column) :

fühlte mich beobachtet, konnte nicht in ruhe auswählen


please suggest solution.It should remove "ich" word only because just matched one whole word only but it is removing word within word.

Answer

In above query you are replacing 'ich' to '' not a exact word so it is taking ich character sequence in whole string , If you want to exact word then you can manage exact word with space like ' ich '.

Your modified query will be,

Option 1

update table1 a
left join table1 b on
    (a.id = b.id)
SET a.column = CASE
         WHEN b.column2 IS NOT NULL THEN REPLACE(a.column, ' ich ', ' ')
       END       
       where (a.column REGEXP '[[:<:]]ich[[:>:]]')

Option 2: you can use concat function also , here you can add space before or after that word which you want to search.

update table1 a
    left join table1 b on
        (a.id = b.id)
    SET a.column = CASE
             WHEN b.column2 IS NOT NULL THEN REPLACE(concat(' ', a.column, ' '), ' ich ', ' ') 
           END       
           where (a.column REGEXP '[[:<:]]ich[[:>:]]')

Note: I'll recommend you use option two to prevent fake space.

Comments