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
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.
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%';
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.
177 isn’t picked up because it has no characters before the 177 – it’s explained well here.
!= 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!
p.s. welcome to the forum!
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂