CODEWITHSUNDEEP
sqloracle-database
topcan5
topcan5

Reputation: 1707

Oracle Join on Delimited String

I have 2 tables: PROFESSOR and STUDENT. Both have a LEVEL columns, which contains "," delimited value. If I have a STUDENT, how can I write a query to return all the PROFESSORs matches at least 1 STUDENT.LEVEL?

For example:

STUDENT[1].LEVEL = '1,2' 
PROFESSOR[1].LEVEL = '2, 3' 
PROFESSOR[2].LEVEL = '2, 4'

I want to return both PROFESSOR 1 and 2.

Upvotes: 0

Views: 112

Answers (1)

AlexSmet
AlexSmet

Reputation: 2161

As D Stanley said you need normalize your data. But anyway, your problem has a solution. Use REPLACE for data clearing and REGEXP for join.

-- data for example
WITH
student AS (
SELECT 'Jon' AS "name", '1,2' AS "level" FROM dual UNION ALL
SELECT 'Bob' AS "name", '1,5' AS "level" FROM dual
),
professor AS (
SELECT 'M.Fowler' AS "name", '2, 3' AS "level" FROM dual UNION ALL
SELECT 'E.Gamma' AS "name", '2, 4' AS "level" FROM dual
)
-- query
SELECT s.*, p.* 
  FROM student s,
       professor p
 WHERE REGEXP_LIKE(p."level", REPLACE(REPLACE(s."level",' ', ''), ',', '|'))

Upvotes: 1

Related Questions