how do I search substring in database?

Question

I have two columns in my database table :

-------------------------------
name            | address
-------------------------------
raj kumar       | park street    
yogin patel     | ghari chowk 
raju singh      | sultan ganj

I would like to retrieve a row containing sultan ganj, but by mistake I search for sultanganj (no space between words). What query will I use in order to get the correct result?

Answer 1

You can REPLACE all spaces in the field address with empty strings and compare them with sultanganj:

SELECT * 
FROM `table`
WHERE REPLACE(`address`, ' ', '') = 'sultanganj'

This will return you :

-------------------------
    name   |   address
-------------------------
raju singh | sultan ganj

Answer 2

Just do like below using REPLACE function to replace the extra space in value

select name, address
from yourtable
where replace(address,' ','') = 'sultanganj'

EDIT:

You can use the same query in answer except that change the WHERE condition to be like where address like '%park%' or address like '%road%'. This way it will match all address which contains either the word park or road or both (example: parkroad,parkstreet,HellRoad,Heavenpark etc).

select name, address
from yourtable
where address like '%park%' or address like '%road%'