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