CODEWITHSUNDEEP
mysqlregex
user3487667
user3487667

Reputation: 529

Find exact word match in a list of word with SQL Query

I have a table like the following table.

entityId |  mId      |  supertTypes                     |  SubTypes
------------------------------------------------------------
12       |  /m.098yu | /m.0yhtg, /m.089hj0__            | Null
13       |  /m.kju_c_| /m.0ypwec, /m.0bnm0__, /m.0hgt   | /m.098yu

My query is:

select mId from table where superTypes= /m.0yhtg;

The answer should be /m.098yu

I wrote the following queres based on page1 and page2but I could not get correct result.

select mId from table where supertypes RLIKE "[[:<:]]/m.0yhtg[[:>:]]";

Select mId from table where supertypes LIKE '/m.0yhtg';

How can I solve this problem?

Upvotes: 0

Views: 646

Answers (1)

juergen d
juergen d

Reputation: 204894

You can use find_in_set for that but that requires a list without whitespaces. So you need to remove them first with replace

Select mId from table 
where find_in_set('/m.0yhtg', replace(supertypes, ' ', '')) > 0

But you should actually rather change your table design. Never store multiple values in a single column!

A better DB design would be adding a new table

entities table
--------------
entityId
mId


types table
----------------
id
name


entity_types table
------------------
entity_id
type_id
is_super_type

Instead of the field is_super_type you could add another table called entity_subTypes and name the entity_types table entity_supertypes instead.

Upvotes: 1

Related Questions