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?

Add Comment
2 Answer(s)

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; 
Answered on September 1, 2020.
Add Comment

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 on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.