Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Converting delimited string into table columns

460798
Member Posts: 130
The source system will send delimited string to my procedure. I need to convert them into table columns to process.
For example
the data for sply_reord_no and order_qty will come as '002R456,100|003Y678,200|093T88,10'
I need to convert this into
Please help.
Thanks in advance
Edited by: Saaya on Nov 12, 2010 10:47 AM
For example
the data for sply_reord_no and order_qty will come as '002R456,100|003Y678,200|093T88,10'
I need to convert this into
sply_reord_no ord_qty ------------------- ------------ 002R456 100 003Y678 200 093T88U 10Dear Experts
Please help.
Thanks in advance
Edited by: Saaya on Nov 12, 2010 10:47 AM
Answers
-
Hi,
See this thread:
947432
for how to split a delimited string into parts, using REGEXP_SUBSTR.
In your case, you'll have to apply this technique twice
(1) to split the full string delimited by '|' into a varaible number of parts
(2) to split each of those sub-qtrings into 2 parts, delimited by ',' (Or you might find it just as easy to to this step using INSTR and SUBSTR.) -
Thanks Frank. Will surely check it out.
-
drop table tbl / create table tbl( sply_reord_no varchar2(20), ord_qty number ) / declare v_str varchar2(100) := '002R456,100|003Y678,200|093T88,10'; begin for v_i in 1..length(regexp_replace(v_str || '|','[^|]')) loop insert into tbl values( regexp_substr(v_str,'[^|,]+',1,2 * v_i - 1), regexp_substr(v_str,'[^|,]+',1,2 * v_i) ); end loop; end; / select * from tbl / SPLY_REORD_NO ORD_QTY -------------------- ---------- 002R456 100 003Y678 200 093T88 10 SQL>
SY. -
Wow . SY you rock !
Thanks a bunch. :-) -
WITH t AS ( SELECT REGEXP_SUBSTR ('002R456,100|003Y678,200|093T88,10', '[^|]+', 1, LEVEL) txt FROM DUAL CONNECT BY LEVEL <= LENGTH ('002R456,100|003Y678,200|093T88,10') - LENGTH ( REPLACE ('002R456,100|003Y678,200|093T88,10', '|')) + 1) SELECT REGEXP_SUBSTR (txt, '[^\,]+', 1, 1) sply_reord_no, REGEXP_SUBSTR (txt, '[^\,]+', 1, 2) ord_qty FROM t
SPLY_REORD_NO ORD_QTY 002R456 100 003Y678 200 093T88 10
-
In 11g:
SQL> with t as (select '002R456,100|003Y678,200|093T88,10' str from dual) -- -- select regexp_substr (str, '(.*),', 1, 1, null, 1) ply_reord_no, regexp_substr (str, ',(.*)$', 1, 1, null, 1) ord_qty from (select regexp_substr (str, '(.*?)($|\|)', 1, level, null, 1) str from t connect by level <= regexp_count (str, '\|') + 1) / PLY_REORD_NO ORD_QTY ------------------------------------------------- -------------------- 002R456 100 003Y678 200 093T88 10 3 rows selected.
-
Nice one Sir !
This discussion has been closed.