Match a delimited substring against a list from a query

I'm looking at a column (Codes) in a mySQL table that has a series of codes delimited by ';'.

Codes

id | Codes
------------
01 | O5.12;Z33.9;A70.2;PR96
02 | A57.0;Z64.88;OA100
03 | O7.99;PR1;CO45


I also have a second set of data: a list of code types and codes belonging to each type. Currently they are organized like this (although this structure can be changed):

CodeType | Code
--------------------
HR-PT | Z33.9
HR-PT | O5.12
NCS | PR96
NCS | CO96


The problem: For each record in the first table, I want to say whether any of the codes belong to a particular set. It's similar to this question, which I solved like this:

select Codes regexp '(^|.*;)(Z33[.]9|O5[.]12)(;.*|$)') as HRPT_Code


However, rather than a static list, I'd like to dynamically pull the HR-PT codes to check against, e.g. using something along the lines of select Code from code_types where CodeType='HR-PT'

select Code from code_types where CodeType='HR-PT'

Any ideas how to accomplish this efficiently?

1 Answer
1

Instead of having to faf around with regexes, you could replace the delimiters with commas (,), and then use find_in_set:

replace

,

find_in_set

SELECT id
FROM codes c
WHERE EXISTS (SELECT *
FROM code_types ct
WHERE ct.code_type = 'HP-PT' AND
FIND_IN_SET(ct.code, REPLACE(c.codes, ';', ',')) > 0)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.