SQL Like statement with regular expressions

Question

My table contains some columns with ;-separated numbers like this :

1;2;43;22;20;12

and so on. It's also possible there's only 1 number in this column like 110, or 2 numbers like this 110;143

I want to select the rows that contain a certain number in this column. The number is in a variable $search_var. Let's say I need to search for the number 1 in my column. If I use a select with like statement like so :

"SELECT * FROM Table WHERE ids LIKE '%".$search_var."%'"

I get all results containing '1' and not only '1', so I also get 11, 14, 110, 1999 etc. I think I need to use some sort of regex-statement but I'm lost here... Who can help ?

What DBMS are you using? SQL Server? PostgreSQL? Oracle? SQLite? MySQL? — STLDev, Jun 18 '14 at 16:32

Brad · Accepted Answer · 2014-06-18T18:48:26.190

2

You might not need regex for this

Set @YourNUmber := 110;

SELECT * 
FROM Table 
WHERE ';' + ids + ';' LIKE '%;'+ @yourNumber + ';%'

This guarantees there are always ; surrounding all the numbers.

This is formatted for SQL Server. The variable syntax and wildcards might be different if you are using something else.

EDIT:

Thanks @FélixGagnon-Grenier for the suggestions. I think either of these two will work. See here for a SQL Fiddle example

SELECT * 
FROM T 
WHERE concat(';',ids,';') LIKE concat('%;', @YourNumber , ';%');


SELECT * 
FROM T 
WHERE LOCATE(concat(';', @YourNumber , ';'),concat(';',ids,';'))>0

edited Jun 18 '14 at 18:48

answered Jun 18 '14 at 16:38

Brad

11,934
4
45
73

Any help on how to transforms this to MySQL ? – Geert Maes Jun 18 '14 at 17:02
I don't know mySQL to answer definitively enough. You could try the answers here through http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference Does it not work as written? (just curious) – Brad Jun 18 '14 at 17:13
`SET` does not take `:` before the `=`, this is when defining variables with `SELECT` – Félix Adriyel Gagnon-Grenier Jun 18 '14 at 17:14
@FélixGagnon-Grenier So it should be like that? (edited) The example in my above comment uses `:=` with `SET` – Brad Jun 18 '14 at 17:16
@Brad it does indeed. but it does not work. and mysql reference does not agree: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html also be sure to put a `;` between different statements, this code would generate an error. – Félix Adriyel Gagnon-Grenier Jun 18 '14 at 17:37
@FélixGagnon-Grenier nice. thanks for the tips. How's the edit look? – Brad Jun 18 '14 at 18:48
Almost perfect, just get the initial `Set` to `SELECT` once again and that's it :) – Félix Adriyel Gagnon-Grenier Jun 18 '14 at 18:57
1

got it thanks to this answer : `WHERE concat(';',Table,';') LIKE concat('%;', '".$search_var."' , ';%')` – Geert Maes Jun 18 '14 at 20:41

score 0 · Answer 2 · edited Jun 18 '14 at 17:16

0

Try this solution if you're using SQL Server. This searches for the number where adjcent characters are not numbers:

SELECT * FROM Table WHERE ids LIKE '%[^0-9]".$search_var."[^0-9]%'

edited Jun 18 '14 at 17:16

STLDev

5,950
25
36

answered Jun 18 '14 at 16:39

Kiran Hegde

3,651
1
16
14

What RDBMS allows regular expressions in LIKE clauses? Most of them have a separate function for that. Also, your example fails if `Name` is *only* a number with no padding around it. – Mr. Llama Jun 18 '14 at 16:41
Edited the table and column name. This works with SQL Server. Read this article http://technet.microsoft.com/en-us/library/ms187489(v=sql.105).aspx – Kiran Hegde Jun 18 '14 at 16:56

SQL Like statement with regular expressions

2 Answers2