art vanderlay art vanderlay - 3 months ago 9
MySQL Question

mysql mariadb LIKE search that gets matching parents

Given a data row structure such as

a <=
aa <=
aaa
aaaa
aaaaa
aab <=
aaba
aabaa
aabb <=
aabba <=
aabbb <=


is there an expression for mysql/mariadb to do a
LIKE
search that matches
aabb%
to get the following results (gets matching parents as well as children)

a
aa
aab
aabb
aabba
aabbb

Answer

A substring match is vastly different than a superstring match (in terms of how the comparison works).

You can still accomplish what you want, however, with two tests and an or clause.

WHERE (column_name LIKE 'aabb%' OR INSTR('aabb', column_name) = 1)