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  |
 |
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 #672177 is a reply to message #672176] |
Fri, 05 October 2018 05:49  |
|
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 |
Solomon Yakobson
Messages: 3227
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 |
 |
Michel Cadot
Messages: 68447
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 |
|
|
|
Goto Forum:
Current Time: Sun May 28 18:13:53 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
