MYSQL Select where string contains keywords

Question

So, I've been doing some digging but I can't really find something specific. Let's say I have a table called products with 2 columns: product_name and vendor.

product_name  |  vendor
Item A        | Computer Mania
Item B        | Incredible Connection
Item C        | Computer Mania

So, now let's say hypothetically I don't remember how to spell computer and I say, okay I remember the name starts with co and ends with er and there is mania afterwards.

How can I build a SELECT query with those conditions in my WHERE? I have no idea how to do it.

Here's what I tried: SELECT * FROM products WHERE vendor_name LIKE '%co%';

However this returns Incredible Connection as well, which I don't want.

Tim Biegeleisen · Accepted Answer · 2020-06-03 14:19:40Z

1

I would use REGEXP here:

SELECT *
FROM yourTable
WNERE vendor REGEXP '^co[a-z]*er mania';

This regex pattern says to find all vendors whose names have a first word starting with co and ending in er, followed by the word mania.

answered Jun 3, 2020 at 14:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maverick · Accepted Answer · 2020-06-03 15:03:56Z

1

You have to use SQL Wildcards properly.

The % wildcard represents zero or more characters. So, if you use %co%, it will search the string co in the vendor column. vendor column in given example in the question has the string co in every row.

If you say that it starts with co and ends with er mania, then use the wildcards as vendor like 'co%er mania'. This will search the vendor column for the values which starts with co and ends with er mania

select * from products where vendor like 'co%er mania';

Refer this to read more about SQL Wildcards.

answered Jun 3, 2020 at 15:03

Maverick

1,59918 silver badges32 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-06-03 14:18:01Z

0

For your example, you would use like:

where vendor_name like 'co%er mania'

You may want to make this case independent as well, although that is the default in MySQL.

answered Jun 3, 2020 at 14:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MYSQL Select where string contains keywords

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related