regex to find string and next character should not be a number

Question

I have a mysql table where in one column the ids of another table is stored as comma separated values.

For eg.

id | name | jobid
---+------+---------
 1 | AA   | 12
 2 | BB   | 
 3 | CC   | 12,124
 4 | DD   | 11,124,46

Here, as you can see the column jobid may be empty, or contain one or more ids separated by comma.

Now I need the get the rows with specific jobid. If I search for the jobid 12, it should return rows 1 & 3.

I can't use WHERE LIKE because as you can see if I search for the jobid 12 it will result rows 1,3 as well as 4.

So what I concluded is to use regex to search the id and the very next or previous character should not be a number. But I am not at all good at regex and I tried a lot a way searching online without any success.

I think my logic is the correct, if not please suggest me the best way to solve this.

NB. For some reason I cannot change the way I store the jobids.

Answer 1

In MySQL, you can simply use string function find_in_set() for this. Its purpose is to search for a value in a comma-separated list (which, as I understood, is what you are looking for).

select * from mytable where find_in_set(12, jobid);