Tags
comma delimited string, comma delimiter, comma separated string, split comma delimited string to rows, split string, sql split comma separated list of values
NOTE : This post is about splitting a single comma delimited string. If you have to apply it on a table with multiple rows having comma delimited strings, then look at Split comma delimited strings in a table using Oracle SQL
This is one of the most common questions in most of the Oracle SQL and PL/SQL forums. Although, there are several examples and demo over the web, I thought to summarize all the approaches together at one place.
- Regular expressions
- XML table
- MODEL clause
- Table function
- Pipelined table function
Using REGULAR EXPRESSIONS
INSTR in CONNECT BY clause
SQL> WITH DATA AS
2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
3 )
4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
5 FROM DATA
6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
7 /
STR
----------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
REGEXP_SUBSTR in CONNECT BY clause
SQL> WITH DATA AS
2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
3 )
4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
5 FROM DATA
6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL
7 /
STR
----------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
REGEXP_COUNT in CONNECT BY clause
SQL> WITH DATA AS
2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
3 )
4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
5 FROM DATA
6 CONNECT BY LEVEL <= regexp_count(str, ',')+1
7 /
STR
----------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Using XMLTABLE
SQL> WITH DATA AS
2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
3 )
4 SELECT trim(COLUMN_VALUE) str
5 FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
6 /
STR
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Using MODEL clause
SQL> WITH t AS
2 (
3 SELECT 'word1, word2, word3, word4, word5, word6' str
4 FROM dual ) ,
5 model_param AS
6 (
7 SELECT str orig_str ,
8 ','
9 || str
10 || ',' mod_str,
11 1 start_pos,
12 Length(str) end_pos,
13 (Length(str) - Length(Replace(str, ','))) + 1 element_count,
14 0 element_no,
15 ROWNUM rn
16 FROM t )
17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
18 FROM (
19 SELECT *
20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str)
21 DIMENSION BY (element_no)
22 MEASURES (start_pos, end_pos, element_count)
23 RULES ITERATE (2000)
24 UNTIL (ITERATION_NUMBER+1 = element_count[0])
25 (start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1,
26 cv(element_no)) + 1,
27 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1,
28 cv(element_no) + 1) ) )
29 WHERE element_no != 0
30 ORDER BY mod_str ,
31 element_no
32 /
STR
------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Using Table function
SQL> CREATE OR REPLACE TYPE test_type
2 AS
3 TABLE OF VARCHAR2(100)
4 /
Type created.
SQL> CREATE OR REPLACE
2 FUNCTION comma_to_table(
3 p_list IN VARCHAR2)
4 RETURN test_type
5 AS
6 l_string VARCHAR2(32767) := p_list || ',';
7 l_comma_index PLS_INTEGER;
8 l_index PLS_INTEGER := 1;
9 l_tab test_type := test_type();
10 BEGIN
11 LOOP
12 l_comma_index := INSTR(l_string, ',', l_index);
13 EXIT
14 WHEN l_comma_index = 0;
15 l_tab.EXTEND;
16 l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,
17 l_index,
18 l_comma_index - l_index
19 )
20 );
21 l_index := l_comma_index + 1;
22 END LOOP;
23 RETURN l_tab;
24 END comma_to_table;
25 /
Function created.
SQL> sho err
No errors.
SQL> SELECT * FROM
2 TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
3 /
COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Using Pipelined Function
SQL> CREATE OR REPLACE TYPE test_type
2 AS
3 TABLE OF VARCHAR2(100)
4 /
Type created.
SQL> CREATE OR REPLACE
2 FUNCTION comma_to_table(
3 p_list IN VARCHAR2)
4 RETURN test_type PIPELINED
5 AS
6 l_string LONG := p_list || ',';
7 l_comma_index PLS_INTEGER;
8 l_index PLS_INTEGER := 1;
9 BEGIN
10 LOOP
11 l_comma_index := INSTR(l_string, ',', l_index);
12 EXIT
13 WHEN l_comma_index = 0;
14 PIPE ROW ( TRIM(SUBSTR(l_string,
15 l_index,
16 l_comma_index - l_index)));
17 l_index := l_comma_index + 1;
18 END LOOP;
19 RETURN;
20 END comma_to_table;
21 /
Function created.
SQL> sho err
No errors.
SQL> SELECT * FROM
2 TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
3 /
COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Hope it helps!
Pingback: Split comma delimited strings in a table using Oracle SQL | Lalit Kumar B
Anonymously said:
Helped me a lot. Thank you!
Lalit Kumar B said:
You’re welcome. Thanks for the feedback.
Bob Barry said:
That was about as easy as it could have been made for me. Thank you very much for the help
Lalit Kumar B said:
Glad it helped!
Aleksey said:
Hello! Thanks for your article!
Could you tell me please how the select will look like if i want to split two strings (into two columns) using XMLTABLE?
Lalit Kumar B said:
Yes, it could be done. But, you need to have fixed number of rows. You could use the same demo and try doing for two columns, however, you need to join the tables with a key and both should have equal number of substr, so that they produce equal number of rows. Also, you need to join using a key to avoid the duplicates.
Aleksey said:
Could you help me to write the select? Let’s say a have two strings:
‘word1, word2, word3’ and ‘word4, word5, word6’
and i want to get two columns:
Lalit Kumar B said:
That should be simple,
magesh said:
Hi LAlit,
For the below query can you please explain how does the connect by level works here…..my understanding is when level is 1 it becomes 1-1 and turns to zero, but am wondering how it displays all..can you kindly explain me this.
WITH DATA AS
( SELECT ‘word1, word2, word3, word4, word5, word6’ str FROM dual
)
SELECT trim(regexp_substr(str, ‘[^,]+’, 1, LEVEL)) str
FROM DATA
CONNECT BY instr(str, ‘,’, 1, LEVEL – 1) > 0
Regards,
Magesh
magesh said:
Also Kindly explain me how does connect by level works in all the above sccenario am really confused about it..Thanks
Lalit Kumar B said:
@Magesh,
It is very simple, and is the basics of hierarchical query.
if you understand how the below query works:
then you will also understand the other queries demonstrated above.
Search google for ROW GENERATOR method and you will understand how CONNECT BY LEVEL is used to generate rows. The only catch is CONNECT BY instr(str, ‘,’, 1, LEVEL – 1) > 0 which means generate as many rows til the instr returns value more than zero.
Sreeram said:
Hi Lalith,
it’s really a good and helpful info
I’ve scenario,
Output should be:-
Lalit Kumar B said:
@Sreeram,
Well, in order to use the above given solutions, you just need to have equal number of delimiters in the string so that they generate equal number of rows. Once you have the data prepared, then the solution remains same.
I am taking your example, and using a CASE expression to manipulate the data to add the required number of delimiters(i.e. comma here). Not very efficient though(you could find an efficient way to do this).
Przemek said:
HI Lalit,
could you help me with the following case?
I want to read CSV file with utl_file – that part is covered, so I’m receving each line from the file. Now I would like to separate the words from each line.
Lines in the file can look like:
“word1”, “work2”, “word3”
“word4”, “work5”, “word6”
In that case the output table should be:
word1 word2 word3
word4 word5 word6
But there can be a comma in the word, for example:
“wo,rd1”, “work2”, “word3”
“word4”, “work5, “wor,d6”
Then the output table should be:
wo,rd1 word2 word3
word4 word5 wor,d6
So, when the comma is in the word I don’t want to split up the word.
I will be grateful for your help.
Best regards,
Przemek
Przemek said:
Hi Lalit,
could you help me with following case?
I have a data row from utl_file.get_line (I’m reading CSV file with comma as default separator, but the separator can be e.g. ; or |).
The separator will be passed as input parameter of my function.
I want to split the row and return the table of words.
Lines in my file can look like:
For example – file1:
“word1”, “word2”, “word3”
“word4”, “word5”, “word6”
file2:
“word1”; “word2”; “word3”
“word4”; “word5”; “word6”
So output table should be (for both files):
word1 word2 word3
word4 word5 word6
If the delimiter is in the word e.g “wor,d1” or “wor;d1”,
the output word should be: wor,d1 or wor;d1
I will be grateful for your help.
Best regards,
Przemek
Waqas Ahmed said:
Hi, sorry to say but most of initial methods will fail for large values
Eg:
WITH CHILD AS
(
SELECT ‘1028528806601,1028528006601,1064089506601,1064532406601,1065373006601,1064076506601,1065396406601,1064531006601,1065374506601,1065029506601,1064044506601,1065005206601,1064085506601,1064077306601,1065030706601,1064085206601,1065037406601,1064530806601,1064086606601,1064572006601,1064042706601,1064046806601,1064567406601,1064042906601,1065023906601,1065383306601,1065392206601,1065022306601,1065004606601,1065021806601,1064077606601,1064531406601,1064083706601,1064571406601,1065660306601,1064082606601,1064085106601,1064081806601,1064082106601,1064072006601,1064085306601,1064073106601,1064071706601,1064532106601,1064087506601,1064568106601,1065039706601,1065658806601,1064046406601,1028526706601,1065396006601,1064043106601,1064080906601,1065389206601,1065659106601,1064086006601,1064042306601,1028526006601,1064530906601,1028527506601,1064571106601,1064574006601,1064072906601,1064074106601,1065005706601,1065012606601,1064079306601,1064564806601,1064075706601,1064570106601,1064563306601,1065022806601,1064562606601,1065037606601,1065025706601,1065389106601,1065387906601,1065387306601,1065386906601,1065386506601,1065379506601,1065383606601,1065393106601,1065392306601,1065027306601,1064046206601,1064530706601,1064562706601,1064074506601,1065039406601,1064566706601,1065026906601,1065388706601,1065387506601,1065393006601,1065657506601,1065658106601,1064089706601,1064078806601,1065658506601,1064088606601,1064089806601,1064084906601,1065388006601,1064089106601,1064076906601,1065374406601,1064042506601,1064081406601,1064077906601,1064081106601,1064082006601,1064076806601,1065037906601,1064043506601,1064081906601,1064574506601,1064080706601,1065393506601,1064080806601,1065037506601,1064082406601,1064568006601,1028525806601,1064042806601,1082547606607,1065005106601,1064570406601,1065036906601,1065005306601,1065395606601,1064043806601,1064043606601,1064530606601,1064043706601,1064530406601,1064573906601,1064530106601,1064081206601,1065030506601,1065390406601,1064041306601,1064085706601,1065374106601,1064044406601,1064087606601,1064088306601,1065385606601,1028528406601,1064073306601,1064075606601,1064045906601,1064563906601,1028527806601,1065027406601,1065658906601,1064073006601,1065005506601,1064081506601,1064072806601,1064532806601,1064081006601,1064566606601,1065038806601,1064086406601,1064086106601,1028526206601,1064567906601,1064570506601,1065013306601,1065003806601,1065374606601,1064085606601,1064574106601,1064046906601,1029344506601,1064083006601,1064043906601,1064042006601,1064086906601,1064086506601,1082549706607,1065395106601,1064044106601,1064046706601,1064043406601,1064090206601,1065393406601,1064042106601,1065027206601,1064085806601,1064085906601,1065391906601,1064532606601,1064088506601,1064546306601,1028526506601,1064079906601,1065003606601,1064075106601,1064083206601,1064087906601,1064085406601,1064080606601,1064571606601,1064568406601,1065390706601,1065023506601,1065390006601,1065390506601,1065397206601,1065392906601,1065657006601,1064573006601,1064567106601,1064574606601,1064082706601,1064043206601,1064084806601,1065036506601,1064083606601,1064530206601,1064574306601,1065394106601,1064044206601,1064046306601,1064046506601,1064079606601,1065397106601,1065660206601,1064567806601,1064567206601,1028528306601,1065023606601,1065025806601,1065024206601,1065027806601,1064575506601,1065024506601,1065026106601,1065024906601,1065025206601,1065036806601,1065037206601,1065039206601,1065038606601,1064072406601,1064567006601,1064574206601,1064563806601,1065022706601,1064562406601,1064567506601,1064568906601,1064569906601,1064569206601,1064569706601,1064570906601,1064564006601,1064571906601,1064574906601,1064044006601,1064529606601,1064572506601,1064575206601,1064572706601,1064573606601,1064565806601,1064075506601,1064089406601,1064084006601,1064071906601,1064084406601,1064090006601,1064084706601,1064086306601,1064087106601,1064079706601,1065657406601,1064071806601,1064090506601,1064087806601,1064088006601,1064074606601,1064076006601,1064076306601,1064078506601,1064076706601,1064077006601,1064566306601,1064078006601,1064073506601,1064082206601,1064083106601,1064079406601,1064080506601,1064083406601,1065022106601,1064046606601,1064573306601,1064072706601,1064072106601,1064568306601,1028528206601,1064077506601,1028525706601,1064088706601,1029346106601,1064090106601,1064085006601,1065387406601,1064074006601,1064568206601,1065022406601,1064040806601,1064041706601,1064041806601,1064041906601,1064046006601,1064040706601,1064530306601,1064089606601,1064046106601,1064075006601,1064534006601,1064086706601,1064087006601,1065394006601,1065026206601,1064087406601,1064075206601,1065022206601,1065031006601,1064040906601,1064041106601,1064041006601,1064041206601,1064531306601,1064571206601,1065395506601,1065027506601,1065658606601,1064043306601,1064571306601,1064042206601,1064041506601,1064041606601,1064562806601,1064077806601,1065658706601,1064069306601,1064574406601,1065020206601,1065659206601,1065659006601,1065657606601,1065657306601,1065396106601,1065395706601,1065391806601,1065394506601,1065396706601,1065380006601,1064532306601,1064532206601,1065038306601,1065392606601,1065393206601,1065393606601,1065389406601,1065393706601,1065394806601,1065392406601,1065397006601,1065029706601,1064089006601,1065386106601,1065386406601,1064573106601,1065386606601,1065387206601,1065385306601,1065387606601,1065388306601,1065388606601,1065390106601,1065389606601,1064074806601,1065013206601,1065030006601,1065032706601,1065030906601,1065031106601,1065031806601,1065032806601,1065031906601,1065019906601,1065020506601,1064077406601,1065021006601,1065021306601,1065025506601,1065026806601,1065037806601,1065027006601,1065019706601,1065022906601,1065023406601,1065023806601,1065026006601,1065024006601,1065027906601,1064575306601,1065024406601,1065026406601,1065024606601,1065024706601,1065025006601,1065036606601,1065037006601,1065037306601,1065039306601,1065038406601,1065038706601,1064561706601,1064083506601,1064572106601,1065022506601,1064568506601,1064567706601,1064568706601,1064570006601,1064569106601,1064569406601,1064570206601,1064570606601,1064570806601,1064561506601,1064571506601,1064571706601,1064574706601,1064575006601,1064572206601,1064572406601,1064572806601,1064572906601,1064080206601,1064573706601,1064573406601,1064565906601,1064073406601,1064566006601,1064562906601,1064073806601,1064084106601,1064084206601,1064072306601,1064084506601,1064074706601,1064087306601,1065039506601,1064082806601,1064090306601,1064090606601,1064088206601,1065388806601,1064088906601,1064078306601,1064075806601,1064076206601,1064076606601,1064077206601,1064073606601,1064078206601,1065030306601,1064073206601,1064082306601,1064078606601,1064078906601,1064079006601,1064079206601,1064079506601,1064080306601,1064081606601,1065021906601,1064563106601,1064042606601,1064072506601,1064077706601,1028526606601,1065031506601,1064567306601,1064089206601,1082558406607,1064566806601,1065374006601,1082554806607,1064079106601,1065020306601,1065657806601,1065657206601,1065396306601,1065395806601,1065392006601,1065391606601,1065394606601,1065395306601,1065396606601,1064532006601,1065381206601,1065038106601,1065392706601,1065393306601,1065389306601,1065393806601,1065394906601,1065392506601,1065029806601,1065386006601,1065386306601,1064573206601,1065386706601,1065387006601,1065387706601,1065388106601,1065388506601,1065388906601,1064080006601,1065390206601,1065389506601,1065003706601,1065030106601,1065031206601,1065009406601,1065031406601,1065031706601,1065032906601,1065032006601,1065020006601,1064089306601,1065020606601,1064072606601,1065021106601,1065021406601,1065025306601,1065021606601,1065026606601,1065022006601,1065019806601,1065023006601,1065023206601,1065023706601,1065025906601,1065024106601,1065027706601,1064575406601,1065024306601,1065026306601,1065024806601,1065025106601,1065036706601,1065037106601,1065037706601,1065039106601,1065038506601,1064074206601,1064566906601,1064563706601,1065022606601,1064568606601,1064567606601,1064568806601,1064569806601,1064569306601,1064569606601,1064570306601,1064570706601,1064571006601,1064571806601,1064574806601,1064572306601,1064561406601,1064572606601,1064573506601,1064565706601,1064075406601,1064083806601,1064575106601,1064084306601,1064089906601,1064084606601,1064086206601,1064087206601,1065039606601,1082540206607,1064090406601,1064087706601,1064088106601,1064088806601,1064078406601,1064075906601,1064076106601,1064076406601,1064077106601,1064569006601,1064078106601,1064074906601,1064081706601,1064074406601,1064079806601,1064080406601,1064083306601,1064044606601,1064081306601,1064072206601,1064563006601,1064082506601,1064083906601,1028525606601,1065030606601,1065385706601,1064088406601,1064573806601,1064086806601,1065020406601,1065657706601,1065657906601,1065657106601,1065396206601,1065392106601,1065394706601,1065391706601,1065395206601,1064073906601,1064073706601,1065038206601,1065392806601,1065393906601,1065395006601,1065029906601,1065386206601,1065386806601,1065387106601,1065385406601,1065387806601,1065388206601,1065388406601,1065389006601,1064080106601,1065390306601,1065389706601,1065030206601,1065030406601,1065031306601,1065010706601,1065031606601,1064082906601,1065032106601,1065020106601,1065020906601,1065020706601,1065021206601,1065021506601,1065025606601,1065021706601,1065026706601,1065038006601,1065033406601,1065018206601,1065023106601,1064078706601,1065023306601’ DATA FROM DUAL
)
SELECT trim(regexp_substr(DATA, ‘[^,]+’, 1, LEVEL)) DATA FROM CHILD
CONNECT BY instr(DATA, ‘,’, 1, LEVEL – 1) > 0
For large data, this always works but it is slow. Could you give any faster method which can work in direct SQL.
WITH CHILD AS
(
SELECT (COLUMN_VALUE).GETNUMBERVAL() SEQ_NUM FROM XMLTABLE(‘1028528806601,1028528006601,1064089506601,1064532406601,1065373006601,1064076506601,1065396406601,1064531006601,1065374506601,1065029506601,1064044506601,1065005206601,1064085506601,1064077306601,1065030706601,1064085206601,1065037406601,1064530806601,1064086606601,1064572006601,1064042706601,1064046806601,1064567406601,1064042906601,1065023906601,1065383306601,1065392206601,1065022306601,1065004606601,1065021806601,1064077606601,1064531406601,1064083706601,1064571406601,1065660306601,1064082606601,1064085106601,1064081806601,1064082106601,1064072006601,1064085306601,1064073106601,1064071706601,1064532106601,1064087506601,1064568106601,1065039706601,1065658806601,1064046406601,1028526706601,1065396006601,1064043106601,1064080906601,1065389206601,1065659106601,1064086006601,1064042306601,1028526006601,1064530906601,1028527506601,1064571106601,1064574006601,1064072906601,1064074106601,1065005706601,1065012606601,1064079306601,1064564806601,1064075706601,1064570106601,1064563306601,1065022806601,1064562606601,1065037606601,1065025706601,1065389106601,1065387906601,1065387306601,1065386906601,1065386506601,1065379506601,1065383606601,1065393106601,1065392306601,1065027306601,1064046206601,1064530706601,1064562706601,1064074506601,1065039406601,1064566706601,1065026906601,1065388706601,1065387506601,1065393006601,1065657506601,1065658106601,1064089706601,1064078806601,1065658506601,1064088606601,1064089806601,1064084906601,1065388006601,1064089106601,1064076906601,1065374406601,1064042506601,1064081406601,1064077906601,1064081106601,1064082006601,1064076806601,1065037906601,1064043506601,1064081906601,1064574506601,1064080706601,1065393506601,1064080806601,1065037506601,1064082406601,1064568006601,1028525806601,1064042806601,1082547606607,1065005106601,1064570406601,1065036906601,1065005306601,1065395606601,1064043806601,1064043606601,1064530606601,1064043706601,1064530406601,1064573906601,1064530106601,1064081206601,1065030506601,1065390406601,1064041306601,1064085706601,1065374106601,1064044406601,1064087606601,1064088306601,1065385606601,1028528406601,1064073306601,1064075606601,1064045906601,1064563906601,1028527806601,1065027406601,1065658906601,1064073006601,1065005506601,1064081506601,1064072806601,1064532806601,1064081006601,1064566606601,1065038806601,1064086406601,1064086106601,1028526206601,1064567906601,1064570506601,1065013306601,1065003806601,1065374606601,1064085606601,1064574106601,1064046906601,1029344506601,1064083006601,1064043906601,1064042006601,1064086906601,1064086506601,1082549706607,1065395106601,1064044106601,1064046706601,1064043406601,1064090206601,1065393406601,1064042106601,1065027206601,1064085806601,1064085906601,1065391906601,1064532606601,1064088506601,1064546306601,1028526506601,1064079906601,1065003606601,1064075106601,1064083206601,1064087906601,1064085406601,1064080606601,1064571606601,1064568406601,1065390706601,1065023506601,1065390006601,1065390506601,1065397206601,1065392906601,1065657006601,1064573006601,1064567106601,1064574606601,1064082706601,1064043206601,1064084806601,1065036506601,1064083606601,1064530206601,1064574306601,1065394106601,1064044206601,1064046306601,1064046506601,1064079606601,1065397106601,1065660206601,1064567806601,1064567206601,1028528306601,1065023606601,1065025806601,1065024206601,1065027806601,1064575506601,1065024506601,1065026106601,1065024906601,1065025206601,1065036806601,1065037206601,1065039206601,1065038606601,1064072406601,1064567006601,1064574206601,1064563806601,1065022706601,1064562406601,1064567506601,1064568906601,1064569906601,1064569206601,1064569706601,1064570906601,1064564006601,1064571906601,1064574906601,1064044006601,1064529606601,1064572506601,1064575206601,1064572706601,1064573606601,1064565806601,1064075506601,1064089406601,1064084006601,1064071906601,1064084406601,1064090006601,1064084706601,1064086306601,1064087106601,1064079706601,1065657406601,1064071806601,1064090506601,1064087806601,1064088006601,1064074606601,1064076006601,1064076306601,1064078506601,1064076706601,1064077006601,1064566306601,1064078006601,1064073506601,1064082206601,1064083106601,1064079406601,1064080506601,1064083406601,1065022106601,1064046606601,1064573306601,1064072706601,1064072106601,1064568306601,1028528206601,1064077506601,1028525706601,1064088706601,1029346106601,1064090106601,1064085006601,1065387406601,1064074006601,1064568206601,1065022406601,1064040806601,1064041706601,1064041806601,1064041906601,1064046006601,1064040706601,1064530306601,1064089606601,1064046106601,1064075006601,1064534006601,1064086706601,1064087006601,1065394006601,1065026206601,1064087406601,1064075206601,1065022206601,1065031006601,1064040906601,1064041106601,1064041006601,1064041206601,1064531306601,1064571206601,1065395506601,1065027506601,1065658606601,1064043306601,1064571306601,1064042206601,1064041506601,1064041606601,1064562806601,1064077806601,1065658706601,1064069306601,1064574406601,1065020206601,1065659206601,1065659006601,1065657606601,1065657306601,1065396106601,1065395706601,1065391806601,1065394506601,1065396706601,1065380006601,1064532306601,1064532206601,1065038306601,1065392606601,1065393206601,1065393606601,1065389406601,1065393706601,1065394806601,1065392406601,1065397006601,1065029706601,1064089006601,1065386106601,1065386406601,1064573106601,1065386606601,1065387206601,1065385306601,1065387606601,1065388306601,1065388606601,1065390106601,1065389606601,1064074806601,1065013206601,1065030006601,1065032706601,1065030906601,1065031106601,1065031806601,1065032806601,1065031906601,1065019906601,1065020506601,1064077406601,1065021006601,1065021306601,1065025506601,1065026806601,1065037806601,1065027006601,1065019706601,1065022906601,1065023406601,1065023806601,1065026006601,1065024006601,1065027906601,1064575306601,1065024406601,1065026406601,1065024606601,1065024706601,1065025006601,1065036606601,1065037006601,1065037306601,1065039306601,1065038406601,1065038706601,1064561706601,1064083506601,1064572106601,1065022506601,1064568506601,1064567706601,1064568706601,1064570006601,1064569106601,1064569406601,1064570206601,1064570606601,1064570806601,1064561506601,1064571506601,1064571706601,1064574706601,1064575006601,1064572206601,1064572406601,1064572806601,1064572906601,1064080206601,1064573706601,1064573406601,1064565906601,1064073406601,1064566006601,1064562906601,1064073806601,1064084106601,1064084206601,1064072306601,1064084506601,1064074706601,1064087306601,1065039506601,1064082806601,1064090306601,1064090606601,1064088206601,1065388806601,1064088906601,1064078306601,1064075806601,1064076206601,1064076606601,1064077206601,1064073606601,1064078206601,1065030306601,1064073206601,1064082306601,1064078606601,1064078906601,1064079006601,1064079206601,1064079506601,1064080306601,1064081606601,1065021906601,1064563106601,1064042606601,1064072506601,1064077706601,1028526606601,1065031506601,1064567306601,1064089206601,1082558406607,1064566806601,1065374006601,1082554806607,1064079106601,1065020306601,1065657806601,1065657206601,1065396306601,1065395806601,1065392006601,1065391606601,1065394606601,1065395306601,1065396606601,1064532006601,1065381206601,1065038106601,1065392706601,1065393306601,1065389306601,1065393806601,1065394906601,1065392506601,1065029806601,1065386006601,1065386306601,1064573206601,1065386706601,1065387006601,1065387706601,1065388106601,1065388506601,1065388906601,1064080006601,1065390206601,1065389506601,1065003706601,1065030106601,1065031206601,1065009406601,1065031406601,1065031706601,1065032906601,1065032006601,1065020006601,1064089306601,1065020606601,1064072606601,1065021106601,1065021406601,1065025306601,1065021606601,1065026606601,1065022006601,1065019806601,1065023006601,1065023206601,1065023706601,1065025906601,1065024106601,1065027706601,1064575406601,1065024306601,1065026306601,1065024806601,1065025106601,1065036706601,1065037106601,1065037706601,1065039106601,1065038506601,1064074206601,1064566906601,1064563706601,1065022606601,1064568606601,1064567606601,1064568806601,1064569806601,1064569306601,1064569606601,1064570306601,1064570706601,1064571006601,1064571806601,1064574806601,1064572306601,1064561406601,1064572606601,1064573506601,1064565706601,1064075406601,1064083806601,1064575106601,1064084306601,1064089906601,1064084606601,1064086206601,1064087206601,1065039606601,1082540206607,1064090406601,1064087706601,1064088106601,1064088806601,1064078406601,1064075906601,1064076106601,1064076406601,1064077106601,1064569006601,1064078106601,1064074906601,1064081706601,1064074406601,1064079806601,1064080406601,1064083306601,1064044606601,1064081306601,1064072206601,1064563006601,1064082506601,1064083906601,1028525606601,1065030606601,1065385706601,1064088406601,1064573806601,1064086806601,1065020406601,1065657706601,1065657906601,1065657106601,1065396206601,1065392106601,1065394706601,1065391706601,1065395206601,1064073906601,1064073706601,1065038206601,1065392806601,1065393906601,1065395006601,1065029906601,1065386206601,1065386806601,1065387106601,1065385406601,1065387806601,1065388206601,1065388406601,1065389006601,1064080106601,1065390306601,1065389706601,1065030206601,1065030406601,1065031306601,1065010706601,1065031606601,1064082906601,1065032106601,1065020106601,1065020906601,1065020706601,1065021206601,1065021506601,1065025606601,1065021706601,1065026706601,1065038006601,1065033406601,1065018206601,1065023106601,1064078706601,1065023306601’)
)
SELECT * FROM CHILD A
Lalit Kumar B said:
You are talking about
which is the string literal limit of SQL and has nothing to do with the solutions provided. All the solutions will work.
In any case, it is a huge design flaw. Why would you store such a huge delimited string as single row. You must normalize it and store as different rows.
pooja sonawane said:
how to use where clause in above mentioned query
Lalit Kumar B said:
Simple, handle it in the FROM clause, instead of table_name use a sub-query and put the WHERE clause in it.
For example,
Kiran said:
Hi lalit,
i have a scenario here, could you please help me get through this ?
i need the output to be as,
thanks in advance,
kiran
Lalit Kumar B said:
The rule to split the delimited rows is not clear. Why is the value ‘mn’ in different column altogether?
kirannk007 said:
Sorry, that was an typo error there,
below is the updated row information.
Lalit Kumar B said:
Kiran,
I am giving you a hint, you should be able to do it yourself.
Read the article again, there is a link "Split comma delimited strings in a table using Oracle SQL"
kirannk007 said:
Sure will try and reply on the outcome.
Thanks for the help.
harry said:
Hi Lalit
I came across a similar scenario and going by all the answer i guess people only taken into account the row values but what if data is huge we use it in the select statement.
What i mean to say i have 5 columns and one of the column got comma separated data which i want to put into rows and the value of rest of the column should remain same.
Could you please provide me a SQL ?
sample data :-
order id sales person client_id date sales_grp_id
123 harry 123 343,345,456
Data i want :-
123 harry 123 343
123 harry 123 345
123 harry 123 456
I can’t use the sales group id in the select column as the data is huge.
Lalit Kumar B said:
Hi Harry,
If performance is your concern, then before writing the SQL to split the rows, you must look into a better design. Load the data in different tables instead of splitting them later.
divya.shamasundar@gmail.com said:
Hi , What if my string has no value between commas. currently this code is skipping if no value
Lalit Kumar B said:
Hi,
Did you try the MODEL approach, it should work for you.
RAMANA said:
Excellent Mr. Lalith Kumar B..
Lalit Kumar B said:
Thank you, hope it helped!
Abhi said:
Lalit,
I need to split comma delimited values to columns instead of rows.
to
I’m wondering if I can do it using xml method.
Thanks
Lalit Kumar B said:
Hi,
It is a FAQ and could be easily done using REGEX. Have a look at https://community.oracle.com/thread/2348338
kunal said:
Hi
I really found this link very useful but when I am trying to do the xml table approach for more than 2 columns it does not work since we are trying to join based on the row number.
Please suggest how can we split the comma separated rows from multiple rows. Data for reference is
Lalit Kumar B said:
Kunal,
You need to split each column separately, you cannot do it at once. I highly doubt the efficiency of the design. Would you consider normalizing it.
Megha said:
I’m trying to pass a comma-separated value (which are actually the values of selected list items in a listbox separated by commas) from C#.NET to oracle stored procedure. The Stored procedure parameter is a CLOB. In the WHERE condition of the query, this CLOB value, which is being stored in VARCHAR2(30000) variable, is being used since the CLOB parameter can’t be directly used in a WHERE condition. The below query is used for splitting the comma separated values into separate record each.
Now the issue which I’m facing is, when the user selects all the ListBox values, the input parameter is amounting to a length of around 23,000. In this scenario, the Stored Procedure is throwing the below error:
The issue could be due to the REGEXP_SUBSTR function which usually takes STRING upto 4000 characters. The V_SELECTED_CUSTOMER variable is a VARCHAR2(30000). Is there a way to make this work for CLOB values as well. Or is there any other alternative by which this can be achieved?
Lalit Kumar B said:
The error is due to the use of VARCHAR2 data type of the INPUT parameter. Which when used as PL/SQL variable in REGEXP_SUBSTR is restricted to 4000 bytes. You can use CLOB, and your output would also be CLOB data type. The string to search in REGEXP_SUBSTR can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The output data type will also be same data type as input.
For example,
It will take a lot of time to execute, so it is only to show that CLOB will work fine.
Pingback: Oracle SQL: CSV Shredding — Simple Code or Simple Efficiency (IT Toolbox Blogs) |
Sinni said:
I have to load a csv file data into a table in oracle. One of the columns of csv file has data more than 4000 characters which has many values. These values can be distinguished from one another by keyword “http” or “https”. I need to split them into different rows in oracle table.
Example of one row in csv file:
abc,123, http://test1 https://test2 http://test3 https://test4
NOTE: Last column value in csv can have more than 4000 characters
When i load this file into a table, it should store like below:
col1 col2 col3
abc 123 http://test1
abc 123 https://test2
abc 123 http://test3
abc 123 https://test4
Lalit Kumar B said:
Looking at your test data, you can use one SPACE as delimiter. Use one of the techniques demonstrated here https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/
Pani said:
Hi Lalit,
I’ve a scenario like this, I have string or DBA Name is ‘ O’NEIL CORPORATION PC LLC LTD AND IT IS OWNED BY 1ST JOHN GEORGE PHD’. The expected output is
‘O’Neil Corporation PC LLC LTD and It is Owned By 1st John George PhD’. There are lookup string like ‘PC LLC LTD PhD is’ applicable. How do I achieve it? Thanks in advance.
Lalit Kumar B said:
You need to provide a complete working test case, and explain the rules to achieve the output. It is impossible to provide a solution without a test case.
Pani said:
Hi Lalit,
Thanks for your help. The requirement is the table has Company Name,Person Name and Address. We need to apply the TitleCase to those columns.
The lookup string set for above columns are,
{
“KPD”, “BW”, “AIC”, “KBI”, “JBL”, “PRL”, “USI”, “ISU”, “USI”,
“LLC”, “LLP”,
“DDS”, “MD”, “DC”, “PPC”, “JV”, “JD”, “DMD”, “PC”, “LMT”, “LLLP”, “LTD”, “ND”, “OD”, “OT”, “PT”, “CMT”, “PCP”, “GP”, “VP”, “CEO”, “CFO”, “CST”, “AG”,
“PMS”,
“II”, “III”, “IV”,
“AC”, “NE”, “NW”, “SE”, “SW”, “PO”,
“OR”, “CA”, “WA”,
“BPOE”, “BHG”, “KOA”, “OHSU”, “AOI”, “TNT”, “MCO”, “GSA”, “AAA”, “IHC”, “ODS”, “OGSERP”, “CC”
}
{“PhD”, “LaCombe”}
{“is”, “in”, “the”, “an”, “of”, “on”, “and”, “de”}
Let say the input for Company Name,
if I will apply initcap
then the required output is
Each word in the input is referred with the given sets. For example LLC,PC is in the above set so it should be uppercase.
Let say the input for First Name
JOHN GEORGE PHD then the output is
John George PhD
Based on the input set the Company Name, Person Name and Address to be modified and stored it into the table.
Please advise.
Lalit Kumar B said:
I think you could easily do it by doing:
1. Split the string.
2. Use CASE expression to check lookup values.
3. Group it back using LISTAGG.
Here is a working test case:
Now you know how to achieve the output, so modify the code as you wish. All the best!
Regards,
Lalit
Pingback: How to transpose a n-length splitted string to n-length columns in PL/SQL?
Roman Bojur said:
Just a little note. For big datasets the recursive queries above fail, “hanging” for a very long time. For example, for about 500 lines with up to 8 elements divided by comma the query didn’t finish for about one hour of running.
I would propose a much easier and quicker solution. No recursion at all, just natural row generator is used:
WITH DATA AS
( SELECT ‘word1, word2, word3, word4, word5, word6’ str FROM dual ),
NATURALS AS (SELECT LEVEL as lev FROM DUAL CONNECT BY LEVEL <= 10)
SELECT trim(regexp_substr(str, '[^,]+', 1, n.lev)) str
FROM DATA d INNER JOIN NATURALS n on 1=1
WHERE regexp_substr(str, '[^,]+', 1, n.lev) is not null
Here INNER JOIN NATURALS n on 1=1 multiplies each row ten times, regexp_substr(str, '[^,]+', 1, n.lev) is taking n-th element, and WHERE condition below removes empty lines when lev is more then the number of sub-elements. Works instantly even on huge datasets.
The only limitation here is that you need to know the maximum number of elements in your data first. You can easily calculate the maximum count of dividers to get it.
Lalit Kumar B said:
Storing multiple values as comma delimited is always a bad design unless there is a strict business case. Data should be properly normalized and loaded accordingly.
dhwani said:
hi mr lalit,
your blog really need full.i have one formula like ab+bc-(2(co)*ab) like this .in my database i have value like ab=10,bc=20,co=2 like that. so i put value on this formula .how can i do .
and formula change dynamically. so i want ans to solve his equation .how can i split and get output.please help me out
dhwani said:
formula pass randomly.and every parameter’s value fetch in database.and put in their place and we get numeric equation than solve formula.but how can solve.
thank you
Lalit Kumar B said:
You could do it in SQL as well as PL/SQL. If values are coming from application, then using PL/SQL you can do some programming. Create a procedure, take values for a,b,c etc. as parameters to the procedure. Then inside the procedure, create your equations using “dynamic sql” which could also be taken as inputs to the procedure.
Coming to your question how to split “ab=10,bc=20,co=2”, you can easily split it using the methods shown above:
dhwani said:
its using comma only for separation. but if we check randomly operator like(+,-,/,%,*) than how can check multiple .and put value in formula also.
Lalit Kumar B said:
Hi,
Can you post a working test case. Provide me create table script, insert statements for data and your desired output with all rules to get the output. I can then help you with SQL to get the desired output.
Regards,
Lalit
Carmen said:
Hi, for example if I have this string
and i want to separate in others strings that only contains 10 characteres what I need to do?
Result
Lalit Kumar B said:
Hi,
Next time please post what have you tried so far..
There are many ways of doing this, in simple SQL you could do it using REGEXP_SUBSTR and CONNECT BY LEVEL clause:
Shashank Jha said:
Hi Lalit ,
I have a requirement like this:
Now if we select the data from this table it will give the result something like this:
However, I am expecting result something like this:
Means it should break the value_text into two or more rows if the character length is more than 30. Also, uda column should have the suffix as 1,2..n
Not sure how to achieve this in a select query.
Any help for resolving this will be highly appreciated.
Lalit Kumar B said:
Hi Shashank,
Appreciate that you posted the DDL and DML statements, this is how a question is supposed to be asked.
Coming to your requirement, what if I say it could be done in the most simple way in SQL*Plus using simple formatting without any SQL code 🙂
However, if you really want them to be split into rows and not just formatting the output, then you could check out the answers in this OTN thread https://community.oracle.com/thread/351300
Mayank Gupta said:
Are there any latest methods in oracle 12c which is mosT efficient among all?
I need it urgently. Thanks
Kurian Jacob said:
Hi Lalit,
Found one more neat way on the net
Lalit Kumar B said:
Hi Kurian,
Thanks for posting it. For general purpose you could use the predefined collection sys.dbms_debug_vc2coll which is in the SYS schema. It is internally created as:
So, in your code you would actually want to create your own collection as per your requirement. I didn’t use this purposefully as I haven’t used collections in my examples as they consume memory. But, thanks for sharing it as it is useful for general purpose to quickly use a predefined collection.
SINGH said:
Hi Lalit,
I have a column which is contains string like (‘125622~20~652456~30~158624~50’)
Engine_ID Enterprise Routing
15 ‘ABC’ ‘125622~20~652456~30~158624~50’
i want to split the above staring into two column like below.
Engine_ID Enterprise Routing Priority
15 ‘ABC’ 125622 20
15 ‘ABC’ 652456 30
15 ‘ABC’ 158624 50
First column will have all the odds postion values and seconds column will contain all even position places values.
my table is having more then 10 lack records could you please guide how to do it in best performance way.
Lalit Kumar B said:
You are mistaking row split with column split. To separate them into different columns, you need to do it manually. I would recommend to review your design instead of doing costly operations on strings.
Kundan said:
it was really useful.. thanks
Lalit Kumar B said:
You’re welcome!
Rama Reddy said:
Hi Lalit,
Can you please help me with the values in new lines?
This one giving me … Needs to trim or suppress the new line
Appreciate
Lalit Kumar B said:
It’s pretty simple. You need to first get rid of the CHR(10)/CHR(13) and then input the proper data to the REGEXP_SUBSTR operation.
So, the below SQL has:
1. An inner query which replaces the unwanted CHR(10) and translates the spaces. Read about TRANSLATE and REPLACE function to understand more.
2. And then the outer query uses the data from #1 output to split the string.
Let’s see how #1 works:
And, now use the above as inner query to the REGEXP_SUBSTR:
Note: The WITH DATA AS is only to produce the data for demo, in your case you just need to use your actual table name.
Rama Reddy said:
Thank you very much for your help
Rama
Pingback: oracle - Función Split en oracle a valores separados por comas con secuencia automática
Swati Joshi said:
Instead of hardcoding values,if i use a SELECT query and pass the values in “conect by ” clause,it is returning lot of values.
WITH DATA AS
( SELECT lv.lookup_code,lv.extattribute1 str
FROM xdc_lookup_types lt,
fgh_lookup_values lv
WHERE lt.lookup_id = lv.lookup_id
AND lt.lookup_code like ‘XXXY’
AND instr(lv.extattribute1,’;’,2)0
)
SELECT lookup_code,trim(regexp_substr(str, ‘[^;]+’, 1, LEVEL)) str
FROM DATA
CONNECT BY instr(str, ‘;’, 1, LEVEL ) >0 ;
Lalit Kumar B said:
The hardcoded values in the WITH clause is only for demo. If you have the data in a table, then look at the examples here https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/
Pingback: oracle - Utilizzo di Oracle PL/SQL Ciclo for Per scorrere delimitato stringa
Cil said:
I simply want to thank you for sharing this.
It helped me.
Lalit Kumar B said:
You are welcome!
santosh said:
Hi Lalith,
I am having small doubt while splitting one column of CLOB type data into multiple columns. Could you please help on this. ( we need to split the data into dynamically but one by one )
Data looks like ‘1,2,3,4,5,6,7,8,9’.
output : 1 as seperate column , 2 as seperate column …….9 as seperate column.
Lalit Kumar B said:
To split it into separate columns you will need to redesign or write the query manually, it is not possible to do it dynamically.
bumpsyndicate said:
Wow. This page (along with the comments questions and especially the replies) has been exceptionally insightful.
Lalit Kumar B said:
Thank you!
pwlm said:
Hi, I have a table that contains strings of letters, e.g. “R;S;T”. I need to convert this to numbers, e.g. “17;18;19”. Another example, “B;Z;BB;DD”, would convert to, “1;25;26;28” – ideally I would like to keep with a SQL based solution.
Would you recommend a solution?
Thanks
Lalit Kumar B said:
That should be easy to achieve. First I will show you the logic to generate the alphabetical sequence to map it to numeric values:
Now, you just need REPLACE or TRANSLATE to get your desired output using above data.
Moumita said:
Hi Lalit, Can you please help me to achieve the below.
I need to populate the pipe separated values into columns. Which is happening through the below Query if all the pipe separated fields having value. But this query is not inserting null value instead its populating the next data. please help
Lalit Kumar B said:
Hi Moumita,
The reason is that, in the connect by clause, the “IS NOT NULL” condition doesn’t allow the NULL values.
Instead you could use REGEXP_REPLACE and do it this way:
Pingback: delimiter - Funzione Split in oracle di valori separati da virgola con sequenza automatica
pragyapandey97 said:
Hi, can you please suggest how reverse can happen in sql? Ex: How 1 2 3 4 5 in different rows be converted to 1,2,3,4,5
Lalit Kumar B said:
You could do it using LISTAGG.
Let’s say this is the sample data:
Now using LISTAGG you can aggregate the rows into single column:
Abhishek said:
Hi Lalit,
I am facing a problem when Inserting the records into a table when using the regular expression mechanism. I am not able to use the xmltable method since my data has & in it and its semicolon separated. The table that I am using has 9K records. I am doing as below:
INSERT into A
WITH DATA AS
( SELECT ‘word1, word2, word3, word4, word5, word6’ str FROM dual)
SELECT trim(regexp_substr(str, ‘[^,]+’, 1, LEVEL)) str
FROM DATA
CONNECT BY regexp_substr(str , ‘[^;]+’, 1, LEVEL) IS NOT NULL
Abhishek said:
I was able to use the xmltable by using REGEXP_REPLACE for the & and got the results that I needed. This article helped me a lot.
Lalit Kumar B said:
Glad it helped!
Hala AbuRob said:
how to make it work for space instead of comma, please
FeeMan said:
sorted out myself thanks
Lalit Kumar B said:
Just use a space instead of comma. Glad it helped!
deivn said:
Hi Lalith,
it’s really a good and helpful info
I’ve scenario,
col1 col2
1 a,b,c
2 d,e,f,g
3 h,i
.
.
.
Output should be:
col1 col2
1 a
1 b
1 c
2 d
2 e
2 f
2 g
3 h
3 i
.
.
.
Lalit Kumar B said:
That should be pretty simple, see https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/
AgnesH said:
Hi Lalit,
Nice work, thanks. Can you please help me with below scenario? I have a string with company names like below,
1. Tata
2. Amul, Inc
So when system is sending it as one string ‘Tata, Amul, Inc’, plsql block is considering string as 3 company names since Amul, Inc has a comma already. Requesting your help on such situations. Appreciate your help 🙂
Lalit Kumar B said:
There has to be some more data points to provide a solution or help you further. For example,
1. Is data in a proper format like JSON or is it a simple string?
2. Are the individual words enclosed within any quotes?
3. What is the delimiter?
Post a real use case with a working test case, as different data would result in a different solution!
ENOCK OLOO said:
good job bro.
Pingback: "PLSQL'DEKİ KODUN KODU CEVAPI - Kod Yanıtları