how to use REGEXP in sql query

Question

I have a column in my table called to_user that the content of it like this:

id     to_user

1.       1+2+3

2.         1

my query

select 
 * from notifications 
 where (type = 4 and to_user LIKE %+".$user_id."+%) 
 or (to_user REGEXP +?".$user_id."+? ) and status = 2

but it doesn't work .

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, Jan 26 '17 at 09:03

ScaisEdge · Answer 1 · 2017-01-26T08:55:25.843

1

you should enclose the strig for search in proper quotes (and remove + if is not part oth string of you sarch condition )

"select * from notifications where (type = 4 and to_user LIKE '%".$user_id."%') 
    or (to_user REGEXP '".$user_id."' ) and status = 2" ;

due the fact you are using string if you n need an exact macth is = istead of like and don't use regex too

edited Jan 26 '17 at 08:55

answered Jan 26 '17 at 08:51

ScaisEdge

131,976
10
91
107

you have a typo ;) please replace ì with ' – Vitalij Mik Jan 26 '17 at 08:52
2

I think the plusses are there to prevent users with part of the ids to be selected. if you search for user id `1` now, user id `215` will also be selected. – Jerodev Jan 26 '17 at 08:54
@Jerodev i have added a brief suggestio for exact match too – ScaisEdge Jan 26 '17 at 08:57

score 1 · Answer 2 · answered Jan 26 '17 at 08:56

1

$sql = "SELECT * 
FROM notifications 
WHERE (type = 4 and to_user 
LIKE '%" . $user_id . "%') 
OR (to_user REGEXP '\+" . $user_id . "\+' ) 
AND status = 2";

answered Jan 26 '17 at 08:56

Vitalij Mik

542
3
6

how to use REGEXP in sql query

2 Answers2