REGEXP for not exist number in comma saperated string

Question

I have a table name tenders which contains the detail like:

+-------------------------------+
|  id     |    Sector           |
+-------------------------------+
|  1      |    1,3,5,9,7        |
+-------------------------------+
|  2      |    2,6,4,7,9,20     |
+-------------------------------+
|  3      |    1,2,3,55,47,52   |
+-------------------------------+
|  4      |    51,2,36,5,4,9,1  |
+-------------------------------+

I used the REGEXP to find the values using mysql

SELECT * FROM `tenders` WHERE sector REGEXP "[[:<:]]1[[:>:]]"

and to set or condition I use

SELECT * FROM `tenders` WHERE sector REGEXP "[[:<:]](1|2)[[:>:]]"

but I want to write a query where sector field neither exist 1 nor 2. output should contain sector every other sector except 1 and 2.

i know about normalization but the DB is remotely connected and cant have any changes on DB. I know i can get the record using mysql query but don't know the syntex. please help — Harshwardhan Sharma, Aug 11 '18 at 07:13
You can do this easily in the PHP code after you run your query. — Sloan Thrasher, Aug 11 '18 at 07:14
Edit your question and show the PHP code that calls the query. — Sloan Thrasher, Aug 11 '18 at 07:15
I know i can do it using PHP. I want this output by query. Because after that i will have to filter the record. which i don't want to do. if there will not be any solution it will be the last way you are saying. — Harshwardhan Sharma, Aug 11 '18 at 07:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177851/discussion-between-harshwardhan-sharma-and-sloan-thrasher). — Harshwardhan Sharma, Aug 11 '18 at 07:18

score 1 · Answer 1 · answered Aug 11 '18 at 09:07

1

Thanks for your answer.

Actually it was so easy to get the output.

I just wrote the query like

SELECT * FROM `tenders` WHERE sector NOT REGEXP "[[:<:]](1|2)[[:>:]]"

and i got the output.

answered Aug 11 '18 at 09:07

Harshwardhan Sharma

261
1
12

This doesn't actually match what you wrote in your answer to @SloanThrasher question 'So, for record 4, you want it to output 51,36,5,4,9". The other answers do attempt to solve that. – Nick Aug 11 '18 at 12:14

Alejandro Nortes · Answer 2 · 2018-08-12T06:56:32.293

0

Depending your MySQL version, you can use REGEXP_REPLACE or REGEXP. TRIM and CONCAT will do the trick (based on 1 and 2):

If your are using MySQL >= 8.0 you can use REGEXP_REPLACE function:

SELECT id, 
TRIM(BOTH ',' FROM REGEXP_REPLACE(CONCAT(',',Sector,','),'(,1)|(,2)','')) 
FROM tenders;

For MySQL Mysql < 8.0 you can use REPLACE function.

SELECT id, 
TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',',Sector, ','),',2',''),',1','')) 
FROM tenders;

You can see a working example on DB Fiddle

edited Aug 12 '18 at 06:56

answered Aug 11 '18 at 07:45

Alejandro Nortes

594
5
17

1

This will replace e.g. 24 with 4 and will merge a number ending in 2 (e.g. 32) with the next number in the list. – Nick Aug 11 '18 at 07:54
You are right @Nick, I changed my Answer using REGEXP_REPLACE – Alejandro Nortes Aug 11 '18 at 08:04
@Nick I also added a working example using REPLACE function. – Alejandro Nortes Aug 11 '18 at 08:23
actually it not about only 1 and 2 there may be any number.And it seems very complex query. – Harshwardhan Sharma Aug 11 '18 at 09:04
@HarshwardhanSharma I just simplified my answer. For MySQL 8 or above you can add more numbers in an easy way, but I agree it's a complex task with MySQL < 8. You should also think if its better to solve this problem creating a MySQL funciton, view or stored procedure and even if it's better to solve this problem using PHP instead of MySQL. Regards! – Alejandro Nortes Aug 11 '18 at 10:09

score 0 · Answer 3 · answered Aug 11 '18 at 08:57

There's no need for regular expressions here, just wrap you Sector column with commas, so every number in a list is between commas, then you can easily check, if some number is there by looking for ,[number], (ex. ,1,) in concatenated column.

Here's query:

select id,
       Sector
from tbl
where locate(',1,', concat(',', Sector,',')) = 0 --1 is not in the list
  and locate(',2,', concat(',', Sector,',')) = 0 --and 2 is not in the list

Demo

score 0 · Answer 4 · answered Aug 24 '18 at 00:22

0

WHERE NOT FIND_IN_SET(1, sector)
  AND NOT FIND_IN_SET(2, sector)

Performance: for 2 items, it might be about the same as NOT REGEXP. For more than 2 it is probably slower. Techniques involving CONCAT will be slower.

answered Aug 24 '18 at 00:22

Rick James

135,179
13
127
222

REGEXP for not exist number in comma saperated string

4 Answers4