CODEWITHSUNDEEP
mysqlsql
garanda
garanda

Reputation: 1291

Select records with spaces in mysql

I have a column which contains many records with one word in common (e.g Channel names):

|        channel        |
--------------------------
| Fox                   |
| Fox Life              |
| Fox News              |
| Fox Action            |
| Fox Movies            |

And the search criteria is: "Channel Fox News"

Now I'd like to select the only row that have Fox News.

I've tried so many things, like split the string by spaces, or using LIKE with no success, because the word "Fox" always select all five records.

note that its has to be word level match.

thanks

Upvotes: 0

Views: 447

Answers (3)

Juan Cota
Juan Cota

Reputation: 1

Just both words with a like should do the trick:

select name from channel where name like '%fox%' and name like '%news%';

Upvotes: 0

Mihai
Mihai

Reputation: 26784

This screams for fulltext index

ALTER TABLE T add FULLTEXT index(channel);

You search with

SELECT * FROM t WHERE MATCH (channel)
    AGAINST ('+Fox +News' IN BOOLEAN MODE);

plus sign means that word HAS to exist in the column There are a couple of things you need to pay attention to:

innodb_ft_min_token_size

the minimum size of the word searched

and stop words

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

How about using like?

where lower(channel) like '%fox news%'

Upvotes: 0

Related Questions