Solved

Oracle SQL -- REGEX / substring / etc ?

Posted on 2013-01-07
6
481 Views
Last Modified: 2013-01-25
Below gets 10,000+ records.

How can I change so it merges
distinct NOTE_ID's into one field,
removing duplicate numbers so only
the below two SUMMARY rows display ?
-------------------------------------
14670      03,04,27,33,34,77,99
14672      11,33,27,99
-------------------------------------
SELECT NOTE_ID, NOTE_TEXT
FROM DOCUMENT_TEXT_TAB
WHERE OUTPUT_TYPE LIKE '%_N'
order by NOTE_ID
s001.jpg
0
Comment
Question by:finance_teacher
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 38751785
if 11gR2 search for str2tbl function on EE and try this...


SELECT   note_id, LISTAGG(s, ',') WITHIN GROUP (ORDER BY s)
    FROM (SELECT DISTINCT note_id, COLUMN_VALUE s
            FROM document_text_tab, TABLE(str2tbl(note_text))
           WHERE output_type LIKE '%_N')
GROUP BY note_id
ORDER BY note_id
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 84 total points
ID: 38751814
I was already working on this and it is pretty much the same but it doesn't use str2tbl.

with mydata as
(
	select distinct note_id, rtrim(regexp_substr(note_text,'([[:alnum:]]*)(,)?',1,column_value),',') note_text_value
	from tab1,
 	table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(note_text)-length(replace(note_text,','))))+1)
  	as sys.odcivarchar2list
  	)
  	)
  	where output_type like '%_N'
)
select note_id, listagg(note_text_value,',') within group (order by note_text_value) from mydata
group by note_id
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 38751820
or, without using str2tbl...

set the connect by level <= 6  to a number sufficiently high that it is at least as big as maximum number of elements in a string.  With your sample data, it's 6 but change as needed

SELECT   note_id, LISTAGG(s, ',') WITHIN GROUP (ORDER BY s)
    FROM (SELECT DISTINCT note_id, REGEXP_SUBSTR(note_text, '[^,]+', 1, n) s
            FROM document_text_tab,
                 (SELECT     LEVEL n
                        FROM DUAL
                  CONNECT BY LEVEL <= 6)
           WHERE output_type LIKE '%_N')
   WHERE s IS NOT NULL
GROUP BY note_id
ORDER BY note_id;
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 38751831
and a 10g/ 11gR1 version if you don't have 11gR2

SELECT   note_id,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", s || ',') ORDER BY s), '/x/text()').getstringval(),
               ','
              )
    FROM (SELECT DISTINCT note_id, REGEXP_SUBSTR(note_text, '[^,]+', 1, n) s
            FROM document_text_tab,
                 (SELECT     LEVEL n
                        FROM DUAL
                  CONNECT BY LEVEL <= 6)
           WHERE output_type LIKE '%_N')
   WHERE s IS NOT NULL
GROUP BY note_id
ORDER BY note_id;
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 83 total points
ID: 38751851
and if note_text containts single  'numbers'   instead of a string of num

with DOCUMENT_TEXT_TAB
as
(
SELECT 14670 NOTE_ID, '03' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '03' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '04' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '33' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '34' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '77' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '99' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '03' NOTE_TEXT from dual union all
SELECT 14670 NOTE_ID, '27' NOTE_TEXT from dual union all
SELECT 14672 NOTE_ID, '11' NOTE_TEXT from dual union all
SELECT 14672 NOTE_ID, '33' NOTE_TEXT from dual union all
SELECT 14672 NOTE_ID, '27' NOTE_TEXT from dual union all
SELECT 14672 NOTE_ID, '99' NOTE_TEXT from dual
)
select note_id, listagg(note_text,',') within group (order by note_text)
from (select distinct note_id, note_text from DOCUMENT_TEXT_TAB
--  WHERE output_type LIKE '%_N' )
group by note_id
order by note_id
/
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 83 total points
ID: 38778994
Just note that to ensure the output tpe ends in '_N' you should escape the underscore to items ending in '1N', for example, are not included.
where output_type like '%\_N' escape '\'
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question