I want to split the row for several by '|' char. like attached screenshots.
From:
to:
Asked
Active
Viewed 32 times
0
APC
- 144,005
- 19
- 170
- 281
Liron Alon
- 21
- 3
-
You can do it using this WITH CTE1 AS ( select COL1, regexp_substr(COL2, '[^|]+', 1, 1) as val1, regexp_substr(COL2, '[^|]+', 1, 2) as val2, regexp_substr(COL2, '[^|]+', 1, 3) as val3, regexp_substr(COL2, '[^|]+', 1, 4) as val4, regexp_substr(COL2, '[^|]+', 1, 5) as val5, regexp_substr(COL2, '[^|]+', 1, 6) as val6 FROM TEST_SPLIT) SELECT COL1, VAL1 FROM CTE1 WHERE VAL1 IS NOT NULL UNION SELECT COL1, VAL2 FROM CTE1 WHERE VAL2 IS NOT NULL UNION SELECT COL1, VAL5 FROM CTE1 WHERE VAL5 IS NOT NULL; – Atif Aug 16 '20 at 13:14
-
1@mohdatif - this question was **closed as a duplicate**. The linked thread includes many different ways of tokenising strings in Oracle SQL, including using regex. Please don't attempt to shoehorn an answer into a comment (which is never a good idea due to poor layout and lack of formatting). Why not upvote one of the better answers in that thread? – APC Aug 16 '20 at 13:54
-
My intention was just to help the user, anyways will take care from future. – Atif Aug 16 '20 at 14:04