You can do it with a recursive CTE
:
WITH cte AS (
SELECT SUBSTR(Units, 1, INSTR(Units || ',', ',') - 1) col,
SUBSTR(Units, INSTR(Units || ',', ',') + 1) value
FROM CbahiHSSpecialtyUnits
WHERE HsSubStandardID=22 AND SpecialtyID = 2
UNION ALL
SELECT SUBSTR(value, 1, INSTR(value || ',', ',') - 1),
SUBSTR(value, INSTR(value || ',', ',') + 1)
FROM cte
WHERE LENGTH(value) > 0
)
SELECT col
FROM cte
WHERE col + 0 > 0
Or, if you know the upper limit of the numbers is, say 20
and there are no duplicates among the numbers:
WITH cte AS (SELECT 1 col UNION ALL SELECT col + 1 FROM cte WHERE col < 20)
SELECT c.col
FROM cte c INNER JOIN CbahiHSSpecialtyUnits u
ON ',' || u.Units || ',' LIKE '%,' || c.col || ',%'
WHERE HsSubStandardID=22 AND SpecialtyID = 2
See the demo.
Results:
col |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…