I have a table with these columns:
| id | name |
|---|---|
| 1 | a |
| 2 | a.b |
| 3 | a.b.c |
| 4 | a.b.c.d |
| 5 | d |
| 6 | d.e |
| 7 | d.e.f |
If I run query:
SELECT id FROM table WHERE name LIKE 'a%'
I can get 1, 2, 3 and 4. However, is it possible to do the reverse, such as:
SELECT id FROM table WHERE prefix_match(name, 'a.b.c')
which will return 1, 2, 3, but not 4.
Does MySQL have such prefix_match logic?
CodePudding user response：
Try this:
SELECT id FROM table WHERE 'a.b.c' LIKE concat(name, '%')
See it work here:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=484dc7c4acee09de5129c4ebe1b47edf
CodePudding user response：
SELECT id FROM `names` WHERE SUBSTR('a.b.c', 1, LENGTH(name)) = name
This will select all the IDs whose corresponding name begins with a substring of 'a.b.c', but only for names that are not longer than 'a.b.c' (5 chars).