- 3,715,496 Users
- 2,242,776 Discussions
- 7,845,366 Comments
Forum Stats
Discussions
Categories
- 15 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 461 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 246 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 124 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Oracle sql split text into rows

aetl
Member Posts: 170 Blue Ribbon
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:
with sample as ( select '{ adam smith class:abcd } { xxxyyyy class:abcd } { zzzz class:abcd }' data from dual ) select rownum, r from sample, lateral( select regexp_substr(data,'{[^}]*}',1,level) r from dual connect by level <= regexp_count(data,'{') ) / ROWNUM R ---------- -------------------- 1 { adam smith class:abcd } 2 { xxxyyyy class:abcd } 3 { zzzz class:abcd } SQL>
And if performance is a factor you can change REGEXP_SUBSTR with SUBSTR + INSTR and REGEXP_COUNT with LENGTH + REPLACE.
SY.
-