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?
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 |
+----------------------------------------------+