Question 238574 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use regular expression or like keyword to query information in MySQL

I have a table called phones with a column like this where items here are repeated

brand
Google
Samsung
Apple
Hydrogen
Nokia
Itel
Honor
Huawei

and I want to use either the LIKE keyword or the regular expression in MySQL to get result that returns only Hydrogen and Honor in the brand column of the table. I have tried the following

select distinct brand from phones where brand like 'h%o%r';

but it returned only Honor instead of both. I’m trying to write a query that starts with ‘H’ and can have letter ‘o’, ‘r’ and even ‘n’ if possible that take any position, not necessarily in order, as is the case with Hydrogen and Honor.
I also tried select distinct brand from phones where brand like 'h%(o|r)'; and it gave an empty set. Help is appreciated.

Tags:

2

Answers


  1. 0

    To retrieve all the rows from the phones table where the brand column contains the letters “H”, “o”, and “r” in any order, you can use multiple LIKE operators combined with the AND operator:

    SELECT brand FROM phones WHERE brand 
LIKE '%H%' AND brand LIKE '%o%' AND brand LIKE '%r%';

    This query uses the LIKE function of the % wildcard character to find the row with the specified character in the brand column. The AND operation is used to ensure that all three conditions are satisfied.

    If your database system supports regular expressions, you can also use a REGEXP function with more complex regular expressions to achieve the same result. Here is an example query that uses the REGEXP function to find rows where the brand column contains the letters “H”, “o” and “r” in any order:

    SELECT * FROM phones WHERE brand 
REGEXP '(?=.*H)(?=.*o)(?=.*r)';
    Login or Signup to reply.
  2. 0

    If I’m understanding the root of the question, you’d like to return only the records with Hydrogen or Honor as the brand. Is there a specific reason you need to use the LIKE operator? Sounds like you can do this a couple of ways:

    1. Use the IN operator:

    select * from phones where brand IN (‘Hydrogen’,’Honor’)

    1. Use the OR operator:

    select * from phones where (brand = ‘Hydrogen’ OR brand = ‘Honor’)

    Let me know if these work!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search