select records where regex equals other column

Question

I've got a table whith integers in column A and strings in column B, like:

+---------+-----------------+
| columnA | columnB         | 
+---------+-----------------+
| 32      | 1,8,12,32       | <--
| 16      | 1,1,2,9,2,7     | 
| 321     | 3,10,56,111,321 | <--
+---------+-----------------+

Is there simple way to select rows where columnB ends with value from columnA?

See also https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Strawberry, Mar 19 '20 at 17:41
NO. Most of the solutions, including the "dup", would incorrectly allow "21" to match the last case. Suggesting reopening. — Rick James, Mar 20 '20 at 03:33

score 1 · Accepted Answer · answered Mar 20 '20 at 03:49

I agree with Gordon's rant against storing a list that way.

FIND_IN_SET() checks for the integer being anywhere in the commalist.

RIGHT() won't check for a suitable boundary. So, "21" would match "3,10,56,111,321". As I understand the Question, only "321" should match.

RIGHT(), plus prefixing with a ',", would have "321" match "3,10,56,111,321" but fail with "321".

Before 8.0, "[[:<:]]321$" could be constructed to use as a regexp' \\b cannot be used.

MySQL 8.0 would not like the above regexp, but could use "\\b321$".

So...

Plan A: Combine some of the above tests and hope you have not left out an edge case.

Plan B: Do as Gordon suggested: fix the schema.

OK, I think this might be the shortest:

WHERE SUBSTRING_INDEX(colB, ',', -1) = colA

mysql> SELECT SUBSTRING_INDEX('321', ',', -1) = '321';
+-----------------------------------------+
| SUBSTRING_INDEX('321', ',', -1) = '321' |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
+----------------------------------------+
| SUBSTRING_INDEX('321', ',', -1) = '21' |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
+-------------------------------------------+
| SUBSTRING_INDEX('7,321', ',', -1) = '321' |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
+----------------------------------------------+
| SUBSTRING_INDEX('7,321,11', ',', -1) = '321' |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+

Thanks for anwser, this edge case was valid in just 170 in around 1.3M results, but still VALID and btw. query execution time dropped from ~1000sec to ~60sec. — marcin.qxv, Mar 20 '20 at 11:28

score 0 · Answer 2 · answered Mar 19 '20 at 17:30

0

Writing this question I got an idea for an anwser using concatenation.

SELECT * FROM myTable t WHERE columnB REGEXP CONCAT(t.columnA, '$');

answered Mar 19 '20 at 17:30

marcin.qxv

23
1
6

1

But "21" would satisfy the last case. – Rick James Mar 20 '20 at 03:32

score 0 · Answer 3 · answered Mar 19 '20 at 17:32

0

You should fix your data model! Do not store numbers as strings! Do not store multiple values in a string column!

That said, sometimes we are stuck with other peoples really, really bad decisions. MySQL has a useful function, find_in_set() for this case:

where find_in_set(columnA, columnB) > 0

answered Mar 19 '20 at 17:32

Gordon Linoff

1,242,037
58
646
786

Thanks for anwser, I'm working on translating an old database. – marcin.qxv Mar 19 '20 at 18:49
OP wants to check for at the end. – Rick James Mar 20 '20 at 03:31

select records where regex equals other column

3 Answers3