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

Question

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?

Answer 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!

• Slimshadddyyy: This understanding is correct - my reading of your question is that you want to eliminate any really short product_ids and not just particular ones. It could be anything unknown but should be eliminated if not matched
• Slimshadddyyy: This would work? - SELECT * FROM p WHERE p_id LIKE '___id%'; where id is passed dynamically
• Vérace: @Slimshadddyyy How do you mean, exactly, "passed dynamically"? What is your coding environment?
• Rick James: @Slimshadddyyy - What client are you using? Or you can build the like string with CONCAT. (We need more details on what you have and what you really want.)