Home » SQL & PL/SQL » SQL & PL/SQL » duplicate part of text message
duplicate part of text message [message #669967] Sun, 27 May 2018 02:09 Go to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi All,

Could you please help me with the SQL Query to fetch rows without repeating the text of the message, if field have part of message same in that table.

Example:
CREATE TABLE my_tbl_dup_text 
  ( 
     emplid   VARCHAR(11), 
     NAME     VARCHAR(40), 
     fld_name VARCHAR(4), 
     descr    VARCHAR(200) 
  );

INSERT INTO my_tbl_dup_text 
VALUES      ('23456', 
             'JAMES', 
             'FLD1', 
             'MISSING STATE, MISSING COUNTRY, MISSING PHONE NO'); 

INSERT INTO my_tbl_dup_text 
VALUES      ('23456', 
             'JAMES', 
             'FLD2', 
             'MISSING STATE, MISSING COUNTRY, MISSING CITY'); 

Assume the text message MISSING STATE or MISSING COUNTRY may have or not in the DESCR field.

I'm expecting output as below:


SELECT DESCR FROM my_tbl_dup_text;

output:
MISSING STATE, MISSING COUNTRY, MISSING PHONE NO, MISSING CITY

Thank You.

Regards
Suji
Re: duplicate part of text message [message #669968 is a reply to message #669967] Sun, 27 May 2018 07:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2910
Registered: January 2010
Location: Connecticut, USA
Senior Member
XML solution:

with t as (
           select  xmlelement("root",xmlagg(xmlelement("a",a))) x
             from  my_tbl_dup_text,
                   xmltable(
                            'ora:tokenize(.,", ")'
                            passing descr || ', '
                            columns a varchar2(25) path '.'
                           )
           )
select  xmlquery(
                 'fn:string-join(
                                 distinct-values(
                                                 for $e in $x/root/a
                                                   return $e
                                                ),
                                 ", "
                                )
                '
                passing x as "x"
                returning content
               ) x
  from  t
/

X
--------------------------------------------------------------
MISSING CITY, MISSING COUNTRY, MISSING PHONE NO, MISSING STATE

SQL> 

SY.
Re: duplicate part of text message [message #669970 is a reply to message #669967] Sun, 27 May 2018 11:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The following assumes that you will want to list the combined texts for each emplid and name and that each of the descriptions are separated by commas.

-- test table and data that you provided:
SCOTT@orcl_12.1.0.2.0> COLUMN name     FORMAT A5
SCOTT@orcl_12.1.0.2.0> COLUMN fld_name FORMAT A8
SCOTT@orcl_12.1.0.2.0> COLUMN descr    FORMAT A50 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> SELECT * FROM my_tbl_dup_text
  2  /

EMPLID      NAME  FLD_NAME DESCR
----------- ----- -------- --------------------------------------------------
23456       JAMES FLD1     MISSING STATE, MISSING COUNTRY, MISSING PHONE NO
23456       JAMES FLD2     MISSING STATE, MISSING COUNTRY, MISSING CITY

2 rows selected.

-- query:
SCOTT@orcl_12.1.0.2.0> COLUMN text     FORMAT A65 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> SELECT emplid, name, LISTAGG (text, ', ') WITHIN GROUP (ORDER BY text) text
  2  FROM   (SELECT DISTINCT t.emplid, t.name, TRIM (REGEXP_SUBSTR (descr, '[^,]+', 1, x.COLUMN_VALUE)) text
  3  	     FROM   my_tbl_dup_text t,
  4  		    TABLE
  5  		      (CAST
  6  			(MULTISET
  7  			  (SELECT ROWNUM
  8  			   FROM   DUAL
  9  			   CONNECT BY LEVEL <= REGEXP_COUNT (t.descr, ',') + 1)
 10  			 AS SYS.ODCINUMBERLIST)) x)
 11  GROUP  BY emplid, name
 12  /

EMPLID      NAME  TEXT
----------- ----- -----------------------------------------------------------------
23456       JAMES MISSING CITY, MISSING COUNTRY, MISSING PHONE NO, MISSING STATE

1 row selected.

-- or if you want them combined for the whole table:
SCOTT@orcl_12.1.0.2.0> COLUMN text     FORMAT A65 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> SELECT LISTAGG (text, ', ') WITHIN GROUP (ORDER BY text) text
  2  FROM   (SELECT DISTINCT t.emplid, t.name, TRIM (REGEXP_SUBSTR (descr, '[^,]+', 1, x.COLUMN_VALUE)) text
  3  	     FROM   my_tbl_dup_text t,
  4  		    TABLE
  5  		      (CAST
  6  			(MULTISET
  7  			  (SELECT ROWNUM
  8  			   FROM   DUAL
  9  			   CONNECT BY LEVEL <= REGEXP_COUNT (t.descr, ',') + 1)
 10  			 AS SYS.ODCINUMBERLIST)) x)
 11  /

TEXT
-----------------------------------------------------------------
MISSING CITY, MISSING COUNTRY, MISSING PHONE NO, MISSING STATE

1 row selected.

Re: duplicate part of text message [message #669971 is a reply to message #669970] Sun, 27 May 2018 12:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2910
Registered: January 2010
Location: Connecticut, USA
Senior Member
That assumes combined list or unique parts never exceeds 4000 bytes.

SY.
Re: duplicate part of text message [message #669974 is a reply to message #669971] Sun, 27 May 2018 17:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Solomon Yakobson wrote on Sun, 27 May 2018 10:23
That assumes combined list or unique parts never exceeds 4000 bytes.

SY.

Ah, good point. I figured there must be some advantage to your XML solution.
Re: duplicate part of text message [message #669984 is a reply to message #669974] Mon, 28 May 2018 05:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2910
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just in using XMLAGG instead of LISTAGG. The rest of it will perform better using multiset or, if OP is on 12C, LATERAL/CROSS APPLY.

SY.
Re: duplicate part of text message [message #669990 is a reply to message #669984] Mon, 28 May 2018 13:45 Go to previous message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you Barbara and Solomon.
Both of you solutions are working prefectly
Previous Topic: Alternative Sql Query
Next Topic: relational() function
Goto Forum:
  


Current Time: Wed Jan 29 03:36:28 CST 2020