Solved

Oracle SQL -- REGEX / substring / etc ?

Posted on 2013-01-07
6
476 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 73

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 76

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 73

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 73

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 31

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now