Match specific string before user input
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?
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;