Skip to content
Advertisement

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-Zd-]' 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?

Advertisement

Answer

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;

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement