match a number using regex for comma separated number

Question

I have a string that contains number with separated by comma like below.

15,22,20,26,33,445,40,44,22,225,115,2

I want to know if a number say 15 is in that string or not.

The problem is that 15 and 115 both are a match.

Same for other number say 2, for this case 20 , 25, and 225 are match.

For both cases only it should return if there is 15 or 2 in the string.

I tried using like keyword but it's not working. It also return the rows with 115 or 20, 225, 222

whille matching 15 and 2 respectively.

Can anyone suggest a regex pattern?

Update

I have a query like below where I was using like keyword, but I was getting wrong result for above reason.

SELECT DISTINCT A.id,A.title,A.title_hi,A.cId,B.id as cid1,A.report_type ,A.icon_img_url, A.created_at , A.news_date 
FROM tfs_report_news A, tfs_commodity_master B
WHERE (',' + RTRIM(A.cId) + ',') LIKE ('%,' + B.id + ',%')
AND A.ccId = B.ccId AND A.`report_type`= "M" 
AND A.isDeleted=0 AND A.isActive=1 AND B.isDeleted=0 
AND B.status=1 
AND A.news_date= (SELECT MAX(T.news_date) 
FROM tfs_report_news T WHERE (',' + RTRIM(T.cId) + ',') 
LIKE ('%,' + B.id + ',%'))
ORDER BY created_at desc, id desc limit 100;

Here tfs_report_news has the string 15,22,20,26,33,445,40,44,22,225,115,2 as column name

cId and individual cId like 15 or 2 is id of tfs_commodity_master

Fix your data model! Don't store numbers as strings! Don't store multiple values in a string column! — Gordon Linoff, Mar 19 '20 at 19:05
I know that @GordonLinoff. It's design flaw in the schema, but I can't make any change there. It's an old app, a lot of business logic depends on this table schema. — sujeet, Mar 19 '20 at 19:07

score 2 · Accepted Answer · answered Mar 19 '20 at 18:48

In MySQL, what you asked for is the purpose of string function find_in_set():

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters [...] Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL.

So to check if a value is present in the list, you can just do:

find_in_set('15', '15,22,20,26,33,445,40,44,22,225,115,2') > 0

Side note: here is a recommended reading.

score 1 · Answer 2 · answered Mar 19 '20 at 18:50

1

Use FIND_IN_SET:

SELECT
    CASE WHEN FIND_IN_SET('15', csv) > 0 THEN 'yes' ELSE 'no' END AS result
FROM yourTable;

Another option would be to use LIKE:

SELECT
    CASE WHEN CONCAT(',', csv, ',') LIKE '%,15,%' THEN 'yes' ELSE 'no' END AS result
FROM yourTable;

Finally, you could also use REGEXP here:

SELECT
    CASE WHEN csv REGEXP '[[:<:]]15[[:>:]]' THEN 'yes' ELSE 'no' END AS result
FROM yourTable;

answered Mar 19 '20 at 18:50

Tim Biegeleisen

502,043
27
286
360

It would be kind of you explaining `'[[:<:]]15[[:>:]]'` – sujeet Mar 19 '20 at 19:40
1

Note that MySQL 8.0.4 changed their regular expression library, one consequence of which is that `[[:<:]]` syntax is not supported anymore. You must use `\\b` for word boundaries. See https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility for other changes. – Bill Karwin Mar 19 '20 at 20:42

match a number using regex for comma separated number

2 Answers2