Solved

Oracle SQL -- REGEX / substring / etc ?

Posted on 2013-01-07
6
478 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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