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

Question

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.

check this link: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Dipanwita Kundu, Apr 18 '16 at 07:02
But what happens when `column2 IS NULL`? But what if there is punctuation next to `ich`? You are better off doing the task in application code. — Rick James, Apr 18 '16 at 14:42

Piyush Gupta · Accepted Answer · 2016-04-19T04:20:33.073

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.

score 0 · Answer 2 · answered Apr 18 '16 at 14:02

0

You may work around that limitation with

REPLACE(concat(' ', a.column, ' '), ' ich ', ' ')

The thing is, you may add fake spaces to the entries and to the word that should be replaced as a whole word. Not a 100% solution, but can work in most cases.

answered Apr 18 '16 at 14:02

Wiktor Stribiżew

607,720
39
448
563

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

2 Answers2