How search two or more words in string?

Question

Hm, I think this will be an easy question but I can't find an answer to it.

I have a column called "name" in a mysql table.

A input might look like "How to programme 13".

I select it like this:

"SELECT * FROM lists WHERE name LIKE '%to programme 13%'"

This returns the row. But I want to be able to search for "how programme" or "how to 13". How can I search for this and make it return a result?

Thanks

Edit:

So basically I have to split the search string variable before searching? I have a search string

$sw = $_GET['sw'];
"SELECT * FROM lists WHERE name LIKE '%".$sw."%'"

Your edit would not find "how to 13". – bart2puck Feb 15 '14 at 05:40 — bart2puck, Feb 15 '14 at 05:40

score 2 · Answer 1 · edited May 23 '17 at 10:33

2

SELECT * FROM lists WHERE name LIKE '%programme%' AND name LIKE '%how%'

you can use REGEXP

 SELECT * FROM lists WHERE name REGEXP 'programme.+how'

edited May 23 '17 at 10:33

Community

answered Feb 15 '14 at 05:28

Jorgesys

Why a -1 :( I'm so sad. – Jorgesys Feb 15 '14 at 05:40
Because `SELECT * FROM lists WHERE name LIKE '%how programme%' AND name LIKE '%how to 13%' `, certainly does not work in this case and returns empty – Alireza Fallah Feb 15 '14 at 05:42
HYPER FUNNY! and what about your answer, that means SELECT * FROM lists WHERE name LIKE '%programme%' AND name LIKE '%how%' this is incorrect too? – Jorgesys Feb 15 '14 at 05:44
She's right Ali, why -1 for my regex approach. – bart2puck Feb 15 '14 at 05:45
I down vote her before she post regex, and as you must know, the first query returns empty row, because there is no row with `how programme` in it – Alireza Fallah Feb 15 '14 at 05:47
I assumed u dinged me too... – bart2puck Feb 15 '14 at 05:48
Elena change and to or. Your query still would fail – bart2puck Feb 15 '14 at 05:49
1

The input "how to programmer 13" does not contain " how to 13" so the 2nd and like would fail the query – bart2puck Feb 15 '14 at 05:56
+1 for changing your mind – Alireza Fallah Feb 15 '14 at 05:59

score 1 · Answer 2 · edited Feb 15 '14 at 06:00

1

 SELECT * from lists where name REGEXP 'how|program|13'

edited Feb 15 '14 at 06:00

Alireza Fallah

answered Feb 15 '14 at 05:29

bart2puck

Siddique Mahsud · Answer 3 · 2014-02-15T05:39:28.613

0

simple repeat condition with OR

SELECT * FROM lists 
WHERE name LIKE '%how programme%'
  OR  name LIKE '%how to 13%'

edited Feb 15 '14 at 05:39

answered Feb 15 '14 at 05:29

Siddique Mahsud

Alireza Fallah · Answer 4 · 2014-02-15T06:04:29.670

-1

This query, searches for rows that have programme AND how

SELECT * FROM lists WHERE name LIKE '%programme%' AND name LIKE '%how%'

edited Feb 15 '14 at 06:04

answered Feb 15 '14 at 05:28

Alireza Fallah

Mr Downvoter! can you say what I missed ? – Alireza Fallah Feb 15 '14 at 05:31
1

Someone is in a stinky mood. All of these answers work... – bart2puck Feb 15 '14 at 05:38

4 Answers4