Match specific string before user input

Question

I have the following strings:

SDZ420-1241242,
AS42-9639263,
SPF3-2352353

I want to "escape" the SDZ420- part while searching and only search using the last digits, so far I've tried RLIKE '^[a-zA-Z\d-]' which works but I am confused on how to add the next digits (user input, say 1241242) to it. I cannot use LIKE '%$input' since that would return a row even if I just input '242' as the search string.

In simple words, a user input of '1241242' should return the row with 'SDZ420-1241242'. Is there any other approach other than creating a separate table with the numbers only?

Strip the prefix before compare: `WHERE SUBSTRING_INDEX(column, '-', -1) = '$input'` — Akina, Aug 25 '20 at 13:09

score 2 · Accepted Answer · answered Aug 25 '20 at 13:06

Note that without jumping through some crazy hoops, this search needs to hit every row in the table; if you have an index on this, it's not going to use that (an index is generally used, assuming it's of the proper kind, which they tend to be, when you search on start, and generally only when using LIKE 'needle%' and not RLIKE. If that's a problem, storing the digits separately, and then putting an index on that, is probably the simplest way to solve your problem here.

To query for the final few digits, why not:

SELECT * FROM foo WHERE colName LIKE ?

with the string made in your programming language via:

String searchTerm = "%-" + digits;

score 0 · Answer 2 · answered Aug 25 '20 at 13:29

0

You can also pass in the number as a string and use:

where substring_index(colname, '-', -1) = ?

This does not require changing the value in the application code.

answered Aug 25 '20 at 13:29

Gordon Linoff

1,242,037
58
646
786

Match specific string before user input

2 Answers2