MySQL – Search Alphanumeric string using wildcards – underscore (_) and asterisk (*)

All we need is an easy explanation of the problem, so here it is.

I have a table, products, with a product_id column as follows:

product_id
110177
110177T
177

I am trying to filter down the product results that should fetch result as below but it is also fetching the id 177

110177
110177T

Query – select * from products where produuct_id like %'177'%

What updates in query to discard the string 177 in search result and only fetch rest of two?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you want to eliminate the 177 value, you have to do the following (see the fiddle here):

CREATE TABLE p (p_id VARCHAR (25));

and populate it:

INSERT INTO p VALUES
('110177'),
('110177T'),
('177');

and then run the following SQL:

SELECT * FROM p WHERE p_id LIKE '___177%';

Result:

p_id
110177
110177T

Note that the ___177% (3 underscores) predicate here will pick up all values that have 3 characters (any single character) followed by 177 followed by any other characters or none.

This is due to the difference between the % (percent) wildcard and _ (underscore) – the _ means that the placeholder represents one, and precisely one character whereas the % wildcard represents 0 or more characters.

So, the 177 isn’t picked up because it has no characters before the 177 – it’s explained well here.

The != solution proposed by @Akina will also work but it implies knowing the values to be excluded in advance – my reading of your question is that you want to eliminate any really short product_ids and not just particular ones!

If you have more sophisticated requirements, you should take a look at regular expressions – an example from PostgreSQL can be found here – MySQL documentation here.

p.s. welcome to the forum!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply