CODEWITHSUNDEEP
mysqlsql
user3717804
user3717804

Reputation: 31

how do I search substring in database?

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?

Upvotes: 3

Views: 109

Answers (4)

Rahul
Rahul

Reputation: 77856

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%'

Upvotes: 1

Gabber
Gabber

Reputation: 7249

SELECT * FROM table where replace(`address`,' ', '' ) like '%your search element%' or `address` like '%your search element%'

and this query working for me

Edit

(1)SELECT * FROM table where replace(`address`,' ', '' ) like '%parkstreet%' or `address` like '%parkstreet%'
(2)SELECT * FROM table where replace(`address`,' ', '' ) like '%park%' or `address` like '%park%'
(3)SELECT * FROM table where replace(`address`,' ', '' ) like '%street%' or `address` like '%street%'
(4)SELECT * FROM table where replace(`address`,' ', '' ) like '%park street%' or `address` like '%park street%'

Upvotes: 0

potashin
potashin

Reputation: 44581

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

Upvotes: 1

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Below query will help you

select * from table1 Where replace(address,' ','') = replace('sultanganj',' ','')

Upvotes: 0

Related Questions