Is it possible to do "reverse" prefix search

Question

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?

Answer 1

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

Answer 2

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).