CODEWITHSUNDEEP
mysqlselectmatch
Taras Bulgakov
Taras Bulgakov

Reputation: 553

MySQL select by mask

There is a MySQL table with content like this:

id mask
1  abcd
2  vdskfw
...
212421 dijhaihwfaw

I have an incoming string, for example, abcdefghijkl, and I need to check, if it begins with any match from the table. I can do it with multiple queries:

SELECT * FROM table WHERE mask = 'abcdefghijkl';

if no match:

SELECT * FROM table WHERE mask = 'abcdefghijk';
SELECT * FROM table WHERE mask = 'abcdefghij';
SELECT * FROM table WHERE mask = 'abcdefghi';

...and so on.

But maybe there is a better way to solve this problem?

Upvotes: 1

Views: 1706

Answers (1)

Andrew Feren
Andrew Feren

Reputation: 66

Any of these will do what you asked for.

SELECT * FROM table
WHERE locate(mask, 'abcdefghijkl') = 1;

or

SELECT * FROM table
WHERE 'abcdefghijkl' rlike concat('^', mask)

or

SELECT * FROM table
WHERE 'abcdefghijkl' like concat(mask, '%')

DEMO

Upvotes: 1

Related Questions