Solved

Oracle SQL -- REGEX / substring / etc ?

Posted on 2013-01-07
6
471 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

13 Experts available now in Live!

Get 1:1 Help Now