Home » SQL & PL/SQL » SQL & PL/SQL » Data spliting (11gr2 )
Data spliting [message #677049] Mon, 12 August 2019 09:35 Go to next message
rajeshkr
Messages: 5
Registered: August 2019
Junior Member
Dear All,

I have a varchar column whose content I have to split based on condition.

E.g
   London 
   SOO
   Val
   Gatwick 

I want to split the data and store in different column as below
Key value
London SOO
Details Gatwick

Input data
CREATE TABLE STG_TEST_DATA(ID NUMBER , VAL VARCHAR2(2000));

INSERT INTO STG_TEST_DATA VALUES( 3,'OTHR
LB
Val
Kenn / Cam Tn via Charing X');

INSERT INTO STG_TEST_DATA VALUES( 2,'CONTACT
LAS
Val
Updated');

Expected Output

id    Key       value
3      OTHR      LB
3      Val       Kenn / Cam Tn via Charing X
2      OTHR      LAS
2      Val       Updated

can some body suggest a solution



[Edit MC: fix code tags and add them to expected output, also add ";" to statements]

[Updated on: Mon, 12 August 2019 10:24] by Moderator

Report message to a moderator

Re: Data spliting [message #677051 is a reply to message #677049] Mon, 12 August 2019 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a way, assuming the "condition" is keys/values are separated by newline character (chr(10)):
SQL> col Key format a10
SQL> col value format a30
SQL> with
  2    data  as (
  3      select id, column_value-1 nb, regexp_substr(val, '[^'||chr(10)||']+', 1, column_value) val
  4      from STG_TEST_DATA,
  5           table(cast(multiset(select level from dual
  6                               connect by level <= regexp_count(val,chr(10))+1)
  7                 as sys.odciNumberList))
  8    )
  9  select id,
 10         max(decode(mod(nb,2),0,val)) "Key",
 11         max(decode(mod(nb,2),1,val)) "value"
 12  from data
 13  group by id, trunc(nb/2)
 14  order by id, trunc(nb/2)
 15  /
        ID Key        value
---------- ---------- ------------------------------
         2 CONTACT    LAS
         2 Val        Updated
         3 OTHR       LB
         3 Val        Kenn / Cam Tn via Charing X

4 rows selected.

Note that code tags end with [/code] not [\code].
Thanks for the Oracle version number and test case.

[Updated on: Sun, 25 August 2019 07:19]

Report message to a moderator

Re: Data spliting [message #677065 is a reply to message #677051] Wed, 14 August 2019 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Data spliting [message #677067 is a reply to message #677065] Wed, 14 August 2019 13:25 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Wed, 14 August 2019 11:24

Any feedback?

OP is 'WORN' (Write Once, Reply Never)?
Re: Data spliting [message #677070 is a reply to message #677067] Thu, 15 August 2019 06:49 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
rajeshkr,

If you refuse to respond with feedback, "It Worked", "It didn't Work", "I found a different solution", or one of your own, people will not continue to help you. Ghosting someone after asking for help is considered very rude.
Re: Data spliting [message #677081 is a reply to message #677070] Fri, 16 August 2019 08:46 Go to previous message
rajeshkr
Messages: 5
Registered: August 2019
Junior Member
Hello All,

sorry for coming back, for some reason i couldn't see the content here.

appreciate all the answer.

Michel your solution is working fine without any changes to query. I feel great with all the great people are available to help out quikly to carry out the our work.

Appreciate once again, it is working as expected
Previous Topic: Materialized View Query
Next Topic: plsql logic
Goto Forum:
  


Current Time: Fri Mar 29 02:52:19 CDT 2024