How to improve applying same filter on multiple columns?

Question

Suppose I have a first table with prefixes of codes I want to exclude from data:

CREATE TABLE PREFIXES (
    PREFIX VARCHAR(10));

INSERT INTO PREFIXES (prefix) VALUES
('M05'),
('M06'),
('M07'),
('M08'),
('M09'),
('M10');

Those prefixes refer to groups of disesases that some patient could have when he visit the hospital. Each visit to the hospital must have a unique main diagnosis and some secondary diagnoses. So I have one table with data about hospital stays:

CREATE TABLE STAYS (
PATIENT VARCHAR(10),
MAIN_DIAG VARCHAR(10),
SECONDARY_DIAG VARCHAR(10));

INSERT INTO STAYS (PATIENT, MAIN_DIAG, SECONDARY_DIAG) VALUES
('1', 'M05xxx', 'R05yyy')
('1', 'M05xxx', 'T98yyy')
('1', 'M05xxx', 'Z12yyy')
('2', 'T05xxx', 'M06yyy')
('2', 'T05xxx', 'R05yyy')
('3', 'A12xxx', 'R12yyy')
('3', 'A12xxx', 'M10yyy')
('3', 'A12xxx', 'T12yyy')
('4', 'R12xxx', 'T12yyy')
('5', 'A12xxx', 'M10yyy')

Now I want to keep any line of the STAYS table where a code starts with one of the prefixes:

CREATE TABLE OUTPUT AS
(SELECT s.*
FROM STAYS s
INNER JOIN PREFIX p
ON (s.MAIN_DIAG LIKE CONCAT(p.PREFIX, '%')))
UNION
(SELECT s.*
FROM STAYS s
INNER JOIN PREFIX p
ON (s.SECONDARY_DIAG LIKE CONCAT(p.PREFIX, '%')));

But this is not very efficient on a large STAYS table, and I have a third column called RELATED_DIAG where I have to look for the prefixes also.

Have you a better way of doing this?

---

Desired output

('1', 'M05xxx', 'R05yyy')
('1', 'M05xxx', 'T98yyy')
('1', 'M05xxx', 'Z12yyy')
('2', 'T05xxx', 'M06yyy')
('3', 'A12xxx', 'M10yyy')
('5', 'A12xxx', 'M10yyy')

Answer 1

Ideally, you'd fix this in your schema design. Since those first 3 characters have an important meaning, they should exist in their own separate column in the STAYS table. Furthermore, that separate column should have a foreign key constraint linking it to the PREFIXES table.