select rows containing a string in a specific column

Question

Content of entire pos column is home01+home03 or home02+home04.
I need to select rows where pos contains home01.

$pos = 'home01';
$stmt = $db->query("select * from banners where pos contains '" . $pos . "'");

Nothing is selected.
Also I need to avoid LIKE statement because of large table.
Any help?

`where left(pos,6) = '.$pos."'"` or you could pass in length of pos as an attribute. — xQbert, Apr 24 '17 at 14:24
@xQbert, content is dinamically changed - is not always the same position of a string. — qadenza, Apr 24 '17 at 14:29
http://stackoverflow.com/questions/2602252/mysql-query-string-contains or use regular expression... looking for example — xQbert, Apr 24 '17 at 14:31

score 2 · Accepted Answer · answered Apr 24 '17 at 14:35

2

You can use match againts

ALTER TABLE table_name ADD FULLTEXT(pos);

SELECT * FROM banners MATCH(pos) AGAINST('+$pos+');

answered Apr 24 '17 at 14:35

Nishant Nair

Shafiqul Islam · Answer 2 · 2017-04-24T14:25:03.193

0

use this query

select * from banners where pos like'%" . $pos . "'";

this query return all row where first match home01

edited Apr 24 '17 at 14:25

answered Apr 24 '17 at 14:24

Shafiqul Islam

Per question, "I need to avoid LIKE statement because of large table." – xQbert Apr 24 '17 at 14:24
`like` is very slow in a large content. – qadenza Apr 24 '17 at 14:25
1

hey then you have to do indexing of that column – Amit Gaud Apr 24 '17 at 14:32
@amit gaud yes if use indexing then if think will be no problem – Shafiqul Islam Apr 24 '17 at 14:33

2 Answers2