MySQL: Match Only Exact Strings in a String from a Table of Key Strings

Question

I'm trying to match words from a table to a string when only the exact word matches, I've only been able to get very loose match so far by using a LIKE statement and it matches most things but the issue is it also matches partial words which I'm trying to avoid.

There isn't always a leading or trailing space.

SQL Query

SELECT GROUP_CONCAT(`keys` SEPARATOR ',')
FROM `table_keys`
WHERE "We Have One Cardboard Train Wheel" LIKE CONCAT('%', `keys`, '%');

`table_keys`

| keys      |
|-----------|
| Car       |
| Wheel     |
| Roof      |

Two matchs are returned which is Car, Wheel which is technically correct but I only want it to match if the whole word is present.

Current Output

Car,Wheel

Wanted Output

Wheel

nbk · Accepted Answer · 2021-04-08 23:02:40Z

0

Regular expressions are Strings

CREATE TABLE table_keys (

  `keys` VARCHAR(5)

);

INSERT INTO table_keys
  ( `keys`)
VALUES
  ('Car'),
  ( 'Wheel'),
  ( 'Roof');

SELECT GROUP_CONCAT(`keys` SEPARATOR ',') AS "keys"
FROM `table_keys`
WHERE "We Have One Cardboard Train Wheel"  
regexp CONCAT('(^|[[:space:]])',`keys`,'([[:space:]]|$)')

| keys  |
| :---- |
| Wheel |

db<>fiddle here

answered Apr 8, 2021 at 23:02

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

llanato Over a year ago

it's a step in the right direction be I can't guarantee there will always be a leading or trailing space.

llanato Over a year ago

This seems to work, REGEXP CONCAT('[[:<:]]' , keys , '[[:>:]]').

Collectives™ on Stack Overflow

MySQL: Match Only Exact Strings in a String from a Table of Key Strings

There isn't always a leading or trailing space.

SQL Query

`table_keys`

Current Output

Wanted Output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

There isn't always a leading or trailing space.

SQL Query

table_keys

Current Output

Wanted Output

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

`table_keys`