0

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?

0

1 Answer 1

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!

4
  • 1
    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 Apr 26, 2021 at 19:47
  • This would work? - SELECT * FROM p WHERE p_id LIKE '___id%'; where id is passed dynamically Apr 26, 2021 at 19:48
  • @Slimshadddyyy How do you mean, exactly, "passed dynamically"? What is your coding environment?
    – Vérace
    Apr 26, 2021 at 20:10
  • @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.)
    – Rick James
    Apr 26, 2021 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.