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 |
|
suji6281
Messages: 134 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 |
Solomon Yakobson
Messages: 3275 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 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 31 23:15:39 CDT 2024
|