I want replace exact word in mysql

Question

I want to replace exact word in mysql.

For example, I want to replace 'u' with 'you' in a sentence. sentence: are u sure. output : are you sure.

I tried use replace but it is also changing 'u' in 'sure'. but i can't get the exact word to replace

Also i want change the cases where u like "u, can" , "can u?" , "u" , "u r a beauty" where every line is in each row of the database and column type is TEXT.

I need update query.

You want to check for word boundaries, you could use regular expressions to match that. See https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — aadibajpai, Aug 13 '19 at 04:33
I tried the regex but there is error that regex replace doesn't exit. can u tell me how to do it. — Black Rose, Aug 13 '19 at 04:44

ScaisEdge · Answer 1 · 2019-08-14T09:42:44.917

You have wrong parameter the replace format in this case is not set columnname = replace(columnname,'you','u') but replace(columnname,'u','you') and you should also check for word boundaries .. ' u ' and you could use also like

update table 
set columnname = replace(columnname,' u ',' you ') 
where columnname  like '% u %' ;

but if you have several cases the you should manage each case properly eg for ' u ' and ' u,'

update table 
set columnname = case when columnname  like '% u %'
                        then  replace(columnname,' u ',' you ') 
                      when columnname  like '% u,%'
                        then  replace(columnname,' u,',' you,') 
                 end
where columnname  like '% u %'
OR columnname  like '% u,%' ;

If you have mysql 8 you could use regexp_replace

 update table 
 set columnname = REGEXP_REPLACE(columnname,'[[:<:]]u[[:>:]]','you') 
 where columnname regexp '[[:<:]]u[[:>:]]';

..

select REGEXP_REPLACE('test u tesp','[[:<:]]u[[:>:]]','you') 
from dual
where 'test u tesp' regexp '[[:<:]]u[[:>:]]';

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/198068/discussion-on-answer-by-scaisedge-i-want-replace-exact-word-in-mysql). — Bhargav Rao, Aug 17 '19 at 04:32

score 0 · Answer 2 · answered Aug 13 '19 at 05:36

If you want an exact word you should include the spaces around the word. so rather than replace(columnname,'you','u') you use replace(columnname,' you ',' u ') That should cover the situation you described

I tried use replace but it is also changing 'u' in 'sure'. but i can't get the exact word to replace

I want replace exact word in mysql

2 Answers2