How can I split a string by comma using oracle sql? Here I have a column which has values like below
123Lcq
Lf32i
jkp32m
I want to split it by comma
1,2,3,L,c,q
L,f,3,2,i
j,k,p,3,2,m
-
Does your data have multi-line strings?trincot– trincot2016-04-10 12:55:30 +00:00Commented Apr 10, 2016 at 12:55
-
Ronnie: you posted a related question yesterday. After you received answers, you posted the following comment: "Thank you so much, it is the most amazing code I have ever seen. Amazing Oracle function. But what a shame I am using Netezza as my database. – Ronnie Lu 18 hours ago." In a follow-up comment I asked you why that question was tagged "Oracle" if you don't use Oracle. Now I see you posted this question ALSO tagged as "oracle." Please explain.user5683823– user56838232016-04-10 22:13:55 +00:00Commented Apr 10, 2016 at 22:13
Add a comment
|
2 Answers
You can achieve the desired output using REGEXP_REPLACE:
SELECT
rtrim(regexp_replace(text, '(.)', '\1,'), ',') result
FROM (
SELECT '123Lcq' text FROM dual UNION ALL
SELECT 'Lf32i' text FROM dual UNION ALL
SELECT 'jkp32m' text FROM dual)
Comments
You could use regexp_replace:
SELECT substr(regexp_replace(mycol, '(.)', ',\1'), 2)
FROM mytable
The regular expression finds every character, and those matching characters are then all prefixed with commas. Finally a simple substr is used to eliminate the first comma.
Note that trimming commas could be an alternative to substr, but the behaviour is different when the original value already has commas at the end of the string: when trimming, you also trim away these original commas.
