How to use REPLACE in mysql query with example
suppose you have a list of Spa Centers in your mysql table.and you want to search in your tables to get similier results that user searched for.if you are looking for a solution for this then this post is for you 😀
so here is a list of all centers in my
center_name column and my user searched for O2 spa.As you can see in my table there are 2 centers with O2 Spa name.But one of them also including desh (-) in its name.so if i use mysql’s
like then it will simply ignore my O2-Spa and will return only O2 SPA HOTEL HILTON.And definitelly i don’t want only 1 result just because a dash (-) is there 🙁 .Mates lets remove the dash 😀 here we go
we will use mysql’s
REPLACE string function to replace column value before matching it to our user’s search string.MySQL REPLACE() replaces all the occurrences of a substring within a string.
REPLACE(str, find_string, replace_with)
|str||A string or column name|
|find_string||A string which is present one or more times within the string str.|
|replace_with||A string which will replace every time it finds find_string within str.|
Mysql Query :
SELECT * FROM centers WHERE REPLACE(center_name,"-"," ") like '%O2 Spa%'
Hope you find this post useful,If it is then please do share with your developer friends 🙂 Happy day