- 3,708,983 Users
- 2,241,169 Discussions
- 7,840,727 Comments
Forum Stats
Discussions
Categories
- 9 Data
- 362.2K Big Data Appliance
- 3 Data Science
- 1K Databases
- 328 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 480 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.7K ORDS, SODA & JSON in the Database
- 409 SQLcl
- 32 SQL Developer Data Modeler
- 184.6K SQL & PL/SQL
- 20.9K SQL Developer
- 1.3K Development
- Developer Projects
- 31 Programming Languages
- 134.6K Development Tools
- 4 DevOps
- 3K QA/Testing
- 174 Java
- 3 Java Learning Subscription
- 6 Database Connectivity
- 64 Java Community Process
- Java 25
- 7 Java APIs
- 141.1K Java Development Tools
- 2 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 132 Java 8 Questions
- 86.1K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 6 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 80 LiveLabs
- 23 Workshops
- 7 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 6 Español
- 1.9K Japanese
- 2 Portuguese
Oracle sql split text into rows

Hello Everyone ,
I have a clob column which has value like this :
{
adam smith
class:abcd
}
{
xxxyyyy
class:abcd
}
{
zzzz
class:abcd
}
class:abcd text is common all between {}
İ want to split into rows after each class:abcd} :
row 1:{
adam smith
class:abcd
}
row2:{
adam smith
class:abcd
}
row3:{
adam smith
class:abcd
}
I tried to use connect by but i didnt do it exactly.
How can i split this text into rows ?
Regards,
Comments
use the function apex_string.split to split clobs. (available since APEX 19.2)
select s.column_value from clob_tab, table(apex_string.split(clob_column,'class:abcd'||chr(10)||'}')) s;
Assuming you are on 12C or higher:
And if performance is a factor you can change REGEXP_SUBSTR with SUBSTR + INSTR and REGEXP_COUNT with LENGTH + REPLACE.
SY.
Duplicate of this