CODEWITHSUNDEEP
mysqlsql
Aletz Morgan
Aletz Morgan

Reputation: 290

MySQL search match at least one word on query string

i'm making a query in mysql but i have a problem: this is my column structure

|country|
--------
Boston
--------
Chicago
--------
washington

The thing is i may have a search item like:

North Washington
Boston Easht
South Chicago

So i'm trying to match it using the %like% operador like that:

select * from am_ciudad where Ciu_Nombre LIKE '%NORTH BOSTON';
select * from am_ciudad where Ciu_Nombre LIKE 'CHICAGO%';
select * from am_ciudad where Ciu_Nombre LIKE '%South Chicago%';

the second one makes match because it starts with "chicago" word, but in the case of the query has a prefix it doesn't, is there a way to search by at least one match in the query string?

Upvotes: 0

Views: 2698

Answers (3)

Pieter
Pieter

Reputation: 1833

IN method

Use comma separated list of your search query:

SELECT * FROM am_ciudad WHERE Ciu_Nombre IN('North', 'Washington', ...)

REGEXP method

I can imagine the REGEXP will be slower, but I haven't benchmarked it.

SELECT * FROM am_ciudad WHERE Ciu_Nombre REGEXP(North|Washington|...)

Upvotes: 3

Vash
Vash

Reputation: 13

Im not sure if your version of mysql supports it but its worth trying.

select * from am_ciudad where Ciu_Nombre in (NORTH,BOSTON);

Same for the others, just replace the space with ','

Upvotes: -1

Simon
Simon

Reputation: 238

Your other searches won't match because they do not exist.

If you want to match Boston in the phrase I love Boston Red Sox then you would need to use ...LIKE '%Boston%'; the %s are wild cards so using them before and after the word you are tying to match tell the query that you don't care what come before and after. Your search string of ...LIKE '%NORTH BOSTON'; is telling query that you are looking for <anything>North BOSTON; which obviously you don't have.

Hopefully that makes sense and helps out.

S

Upvotes: 0

Related Questions