CODEWITHSUNDEEP
mysqlsql
Jones
Jones

Reputation: 197

sql names containing 2 or more charaters

This is a question for learning sense more than I need to find a solution. I have a product entity, and it has an attribute of product_name. I would like to search product_name and return all products that have more than 1 'a' in the product_name. I need help with the query for this.

This code shows all products that have 'a'

SELECT product_name AS 'Product Names' FROM product WHERE name LIKE '%a%'

BUT I'm after products that have more than 1 'a'. Structure: Product

Product(p_Id, product_name, p_cost)

So if I had a product called "Car Manual" it would return as there are 3 'a's. Please help.

Upvotes: 3

Views: 138

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Another trick you could use is to remove all 'a' characters in the name column and then check to see if the length has decreased by two or more:

SELECT product_name AS ProductNames
FROM product
WHERE LENGTH(REPLACE(name, 'a', '')) <= LENGTH(name) - 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The query would look like:

SELECT product_name AS ProductNames
FROM product
WHERE name LIKE '%a%a%';

Don't use single quotes for column aliases. The best thing to do is to name the columns so they don't need to be escaped. Otherwise use backticks, double quotes, or square braces, depending on your database.

Upvotes: 3

Related Questions