Home » SQL & PL/SQL » SQL & PL/SQL » If the sting value more then 3 separated need to split into 2nd line
If the sting value more then 3 separated need to split into 2nd line [message #672175] Fri, 05 October 2018 05:41 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I have one of column is like below.

with tt
as(select '11111,22222,33333,44444,55555,666666,777777,888888' str from dual
union all
select '101010,202020,303030,404040' str from dual
)
select str
from tt

If the sting value more then 3 separated need to split into 2nd line

Expected output
================
11111,22222,33333,
44444,55555,666666,
777777,888888
101010,202020,303030,
404040

Oracle 11.2 version.

Please let me know if any questions
Re: If the sting value more then 3 separated need to split into 2nd line [message #672176 is a reply to message #672175] Fri, 05 October 2018 05:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Please let me know if any questions
What SQL have you tried so far?
Re: If the sting value more then 3 separated need to split into 2nd line [message #672177 is a reply to message #672176] Fri, 05 October 2018 05:49 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi John,

I used below SQL
SELECT    REGEXP_SUBSTR (:str, '[^,]+', 1, 1)
       || ','
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 2)
       || ','
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 3)
       || ','
       || CHR (10)
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 4)
       || ','
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 5)
       || ','
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 6)
       || ','
       || CHR (10)
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 7)
       || ','
       || REGEXP_SUBSTR (:str, '[^,]+', 1, 8)
  FROM DUAL;

I am getting expected output when i use 8 values

But getting like if i have 4 values
101010,202020,303030,
404040,,,
,
Re: If the sting value more then 3 separated need to split into 2nd line [message #672180 is a reply to message #672177] Fri, 05 October 2018 07:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with tt
as(select '11111,22222,33333,44444,55555,666666,777777,888888' str from dual
union all
select '101010,202020,303030,404040' str from dual
)
select  regexp_substr(str,'([^,]+,){0,2}[^,]+',1,occurrence) str
  from  tt,
        lateral(
                select  level occurrence
                  from  dual
                  connect by level <= trunc(regexp_count(str,',') / 3) + 1
               )
/

STR
------------------------------
11111,22222,33333
44444,55555,666666
777777,888888
101010,202020,303030
404040

SQL>

SY.
Re: If the sting value more then 3 separated need to split into 2nd line [message #672181 is a reply to message #672175] Fri, 05 October 2018 07:26 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Without REGEXP functions:
SQL> with tt
  2  as(select '11111,22222,33333,44444,55555,666666,777777,888888' str from dual
  3  union all
  4  select '101010,202020,303030,404040' str from dual
  5  )
  6  select str,
  7         substr(str,
  8                decode(column_value,
  9                       1, 0,
 10                       instr(str||',,,', ',' , 1, 3*(column_value-1)))+1,
 11                instr(str||',,,', ',', 1, 3*column_value)
 12                - decode(column_value,
 13                         1, 0,
 14                        instr(str||',,,', ',', 1, 3*(column_value-1)))
 15               ) val
 16  from tt,
 17       table(cast(multiset(select level from dual
 18                           connect by level <= ceil(regexp_count(str||',',',')/3))
 19                  as sys.odciNumberList))
 20  order by 1, column_value
 21  /
STR                                                VAL
-------------------------------------------------- --------------------------------------------------
101010,202020,303030,404040                        101010,202020,303030,
101010,202020,303030,404040                        404040
11111,22222,33333,44444,55555,666666,777777,888888 11111,22222,33333,
11111,22222,33333,44444,55555,666666,777777,888888 44444,55555,666666,
11111,22222,33333,44444,55555,666666,777777,888888 777777,888888

[Updated on: Fri, 05 October 2018 07:28]

Report message to a moderator

Previous Topic: regular expression substring to get the string that is delimited by a comma
Next Topic: How to read correctly the execution plan in oracle, both in tree and tabular mode?
Goto Forum:
  


Current Time: Thu Mar 28 10:15:04 CDT 2024