Keep records that match variable length codes-CodePudding

This problem originated from working with ICD-9 and ICD-10 classification codes.

Given a set of code prefixes of variable length, return all records from a table that match these codes. Assume the list of codes is long enough that it is stored in a lookup table.

For example:

code_prefixes char_length
   A12          3
   B123         4

My naive approach was to use SUBSTRING and list each different length as follows:

SELECT *
FROM main_table
WHERE SUBSTRING(code, 1, 3) IN (SELECT code_prefix FROM lookup_table WHERE char_length = 3)
OR SUBSTRING(code, 1, 4) IN (SELECT code_prefix FROM lookup_table WHERE char_length = 4)

But this requires duplication for every different length value in code_prefix and it assumes that char_length is always correct.

CodePudding user response：

Here is the best solution I have come up with so far:

SELECT *
FROM main_table AS M
WHERE EXISTS (
    SELECT 1
    FROM lookup_table AS L
    WHERE SUBSTRING(M.code, 1, LEN(L.code_prefix)) = L.code_prefix
)

CodePudding user response：

You can just use LIKE in an EXISTS subquery

SELECT *
FROM main_table AS M
WHERE EXISTS (SELECT 1
    FROM lookup_table AS L
    WHERE M.code LIKE L.code_prefix   '%'
);