MySQL string searching technique?

Question

In my android app, I have a search field where the user can type something. They can type anything they want like names, addresses, phone numbers, emails, etc... in my database.

I want it to be like an all in one search bar in google search, so they can type combinations of things like a name with an address for example.

The problem is how would I do a search where the search text can contain multiple phrases?

For example if the user searched "Jack Daniel [email protected]", then I would want mysql select statement to end up being like:

select id from members
where firstname=`Jack` or firstname=`Daniel` or firstname=`[email protected]`
   or lastname=`Jack` or lastname=`Daniel` or lastname=`[email protected]`
   or email=`Jack` or email=`Daniel` or email=`[email protected]`

Basically each phrase of the search text needs to be compared with all the columns.

Does MySQL have a built in mechanism to do this easily, or do I have to parse the string in java and manually build the sql statement?

Thanks

You probably will need to parse before going to SQL, like for e-mail, you need parse it locally if it can be typed or not in any place of the search phrase. You can not distinguish what is a name, or an address, other than the formating the user can do, so depending on that, you can need to search in other fields. — Diego C Nascimento
– Diego C Nascimento, Commented Sep 10, 2013 at 3:36

Thomas W · Accepted Answer · 2013-09-10 04:58:44Z

1

Users like a "quick search", and for usability I think this works best matching anywhere in the string -- therefore a LIKE '%Jack%' condition.

I don't bother to separate fields & clauses out for the "quick search". (For a full-weight search form I have separate inputs for each field the user can search on.) But quick search is a simple UI.

I do it case-insensitive. If it's not your DB's default, you can use a lower() function on the string before matching.

For input 'Jacks':

select * from members 
where fullname like '%Jacks%' or email like '%Jacks%'

Or, like I do in FireBird to force case-insensitivity:

select * from members 
where lower(fullname) like '%jacks%' or lower(email) like '%jacks%'

This would match all of:

fullname='Mike Jackson'
fullname='Jackson Five'
fullname='Lumberjacks R Us'
email='[email protected]'

You can keep the first and lastname fields separate, if you want.. my designs prefer to keep them together, since I mainly just use the fullname for display purposes & don't care as to distinctions.

edited Sep 10, 2013 at 4:58

answered Sep 10, 2013 at 3:47

Thomas W

14.2k4 gold badges64 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

omega Over a year ago

so when you say LIKE, that means is a substring of?

Thomas W Over a year ago

% is the multi-character wildcard in LIKE, equivalent to * in filenames (_ is equivalent to ?). LIKE 'a%' matches strings starting with 'a', LIKE '%a' matches strings ending with 'a', and LIKE '%a%' matches 'a' anywhere in the string.

Thomas W Over a year ago

And just wondering about case sensitivity -- since SQLite comes with the Android platform, why are you asking about MySQL?

omega Over a year ago

Im using a free web server that uses php and mysql. Also is this condition true: jack LIKE %jack daniels%? (jack daniels would be the search text, and jack would be the columns value)

Thomas W Over a year ago

No, "%jack daniels%" is too specific & too long to match just "jack". If your users want Jack Daniels, they'll probably search for "daniels" or "jack" first anyway -- they typically search for a short distinctive word or phrase first. If they're specifically looking for "daniels", they don't want to see just Jack.

user1509803 · Accepted Answer · 2013-09-10 03:32:03Z

0

I think the wild card will help you a little bit. Check it out here: http://www.w3schools.com/sql/sql_wildcards.asp

My suggestion is try adding a column named FULLNAME for example, then can use something like this:

select id from members where
fullname='%Jack%' or fullname="%Daniel%" or email="%jack%" or email="%daniel%"

answered Sep 10, 2013 at 3:32

user1509803

1961 silver badge9 bronze badges

Collectives™ on Stack Overflow

MySQL string searching technique?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related