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?

combine `replace`, and `like` in `where` clause. Example: `select * from table_name where replace( address, ' ', '' ) like '%sultanganj%';` — Ravinder Reddy, Jun 07 '14 at 13:07
Look into Levenshtein distance if you're doing some sort of search function — Jordan Doyle, Jun 07 '14 at 13:08
MySQL does not contain a Levenshtein function, but there is an implementation listed here: http://stackoverflow.com/questions/13909885/how-to-add-levenshtein-function-in-mysql This is better, imho, that the answers involving replacing spaces, at it will help with more general mispellings. — John Powell, Jun 07 '14 at 13:15

potashin · Answer 1 · 2014-06-07T13:13:34.460

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

edited Jun 07 '14 at 13:13

answered Jun 07 '14 at 13:07

potashin

44,205
11
83
107

thanks for answer but there is one more problem suppose I searched for parkroad(no space between the word) and there is no such word in my database but still I want to recieve rows either containing park or road or both.Then which query will I use ? plz answer – user3717804 Jun 09 '14 at 11:52

Rahul · Answer 2 · 2014-06-10T11:55:07.263

1

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

edited Jun 10 '14 at 11:55

answered Jun 07 '14 at 13:08

Rahul

76,197
13
71
125

thanks for answer but there is one more problem suppose I searched for parkroad(no space between the word) and there is no such word in my database but still I want to recieve a row either containing park or road or both.Then which query will I use ? plz answer – user3717804 Jun 09 '14 at 11:53
@user3717804, 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%'`. – Rahul Jun 09 '14 at 11:58
which query you used to break single word(parkroad) into two word (park,road)? – user3717804 Jun 10 '14 at 07:28

score 0 · Answer 3 · answered Jun 07 '14 at 13:07

0

Below query will help you

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

answered Jun 07 '14 at 13:07

Pragnesh Khalas

2,908
2
13
26

thanks for answer but there is one more problem suppose I searched for parkroad(no space between the word) and there is no such word in my database but still I want to recieve a row either containing park or road or both.Then which query will I use ? plz answer – user3717804 Jun 10 '14 at 07:30

Gabber · Answer 4 · 2014-06-10T11:44:57.670

0

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

edited Jun 10 '14 at 11:44

answered Jun 07 '14 at 13:13

Gabber

7,169
3
32
46

thanks for answer but there is one more problem suppose I searched for parkroad(no space between the word) and there is no such word in my database but still I want to recieve a row either containing park or road or both.Then which query will I use ? plz answer – user3717804 Jun 10 '14 at 07:29
@user3717804 you can use the same query in this answer – Gabber Jun 10 '14 at 07:34
@user3717804 i edit the answer and also check this query for search element "park" ,"road","parkroad" and "park road" and it working for me.. – Gabber Jun 10 '14 at 08:50
tell me query how did you break the word parkroad into park,road – user3717804 Jun 10 '14 at 11:03
ok basically I think u did not get my question so let me clarify it for you.so i am searching for "parkroad" and in my database if u see above there is no such word but there is "park street".so instead of getting no result what i want is to recieve the rows containing park or road. I m not giving input again and again .I have given the input once "parkstreet".so now you give the query how you broke parkstreet into two words park,street – user3717804 Jun 10 '14 at 11:12

how do I search substring in database?

4 Answers4