Home » SQL & PL/SQL » SQL & PL/SQL » Split Comma seperated Value
Split Comma seperated Value [message #680716] Tue, 02 June 2020 20:31 Go to next message
vharish006
Messages: 68
Registered: August 2015
Location: Chicago
Member
Hi All,

I'm trying to Separate based on Semicolon.

WITH
      INV as (
         select INVOICE_CONTACT,
                to_char(regexp_substr(INVOICE_CONTACT,'[^;]+',1,LEVEL)) INVOICE_CONTACT FROM 
                (SELECT * FROM  XXPQ_IP_INVOICE_HEADER_EXT),table(cast(multiset(Select level from dual
connect by  level < regexp_count(INVOICE_CONTACT,';')-1)  as sys.odciNumberList)) where rownum =1 )
SELECT DISTINCT hdr.payer_number PAYER_NUMBER,ext.invoice_contact INVOICE_CONTACT
FROM XXPQ_IP_INVOICE_HEADER hdr,apps.ra_customer_trx_all ract,XXPQ_IP_INVOICE_HEADER_EXT ext
 WHERE  hdr.pq_customer_trx_id=ract.customer_trx_id
 AND    hdr.client_invoice_id=ext.client_invoice_id
 AND    RACT.CREATION_DATE>TRUNC(SYSDATE-1095)
 AND    ext.invoice_contact is not null
 and   hdr.payer_number='147690'
BELOW IS HOW THE RESULT IS APPEARING:
PAYER_NUMBER     INVOICE_CONTACT
 
12345            XXXX@gmail.com;YYY@gmail.com
12345            YYY@gmail.com
Can any one suggest what should change in above query to seperate these in to seperate records.


[EDITED by LF: fixed [code] tags]

[Updated on: Thu, 11 June 2020 15:18] by Moderator

Report message to a moderator

Re: Split Comma seperated Value [message #680717 is a reply to message #680716] Wed, 03 June 2020 00:03 Go to previous message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Wed, 21 August 2019 21:58

From your previous topic:

Michel Cadot wrote on Thu, 15 August 2019 19:35

Why didn't you post the solution here?
And why didn't you feedback in your previous topics?
Still waiting...

And in another previous one:

Michel Cadot wrote on Wed, 19 December 2018 21:27

No feedback in your previous topics.
No format.
Not posting what is required.
No help.

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Wed, 03 June 2020 00:05]

Report message to a moderator

Previous Topic: Error ORA-00932 when viewing data from an OLAP cube
Next Topic: BLOB msg print in xml format
Goto Forum:
  


Current Time: Sat Sep 26 00:23:47 CDT 2020