Home » SQL & PL/SQL » SQL & PL/SQL » Merge String (Oracle 11g)
Merge String [message #641659] |
Thu, 20 August 2015 01:18 |
Xandot
Messages: 235 Registered: January 2014 Location: India
|
Senior Member |
|
|
Hi All,
I have two input strings like:
String 1: ADAMS,JAMES,MILLER,MARTIN
String 2: ADAMS,JAMES,MILLER,MARTIN,FORD,BLAKE
I want to merge these string 1 & 2 into one string like:
String
----------------
ADAMS,JAMES,MILLER,MARTIN,FORD,BLAKE
Please help me out.
Thanks,
Xandot
[Updated on: Thu, 20 August 2015 01:23] Report message to a moderator
|
|
|
Re: Merge String [message #641660 is a reply to message #641659] |
Thu, 20 August 2015 01:28 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
You could first split the delimited string into different rows and then apply LISTAGG.
For example,
SQL> WITH t(text) AS(
2 SELECT 'ADAMS,JAMES,MILLER,MARTIN' FROM dual UNION ALL
3 SELECT 'ADAMS,JAMES,MILLER,MARTIN,FORD,BLAKE' FROM dual
4 )
5 SELECT listagg(text, ',') within GROUP(
6 ORDER BY NULL) text
7 FROM
8 ( SELECT DISTINCT trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
9 FROM t,
10 TABLE (CAST (MULTISET
11 (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
12 ) AS sys.odciNumberList ) ) lines
13 )
14 /
TEXT
--------------------------------------------------------------------------------
ADAMS,BLAKE,FORD,JAMES,MARTIN,MILLER
SQL>
|
|
|
Re: Merge String [message #641661 is a reply to message #641660] |
Thu, 20 August 2015 01:42 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
If you want to retain the order of the elements,
SQL> WITH t(text) AS(
2 SELECT 'ADAMS,JAMES,MILLER,MARTIN' FROM dual UNION ALL
3 SELECT 'ADAMS,JAMES,MILLER,MARTIN,FORD,BLAKE' FROM dual
4 )
5 SELECT listagg(text, ',') WITHIN GROUP(
6 ORDER BY id) text
7 FROM
8 ( SELECT DISTINCT lines.column_value id,
9 trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
10 FROM t,
11 TABLE (CAST (MULTISET
12 (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
13 ) AS sys.odciNumberList ) ) lines
14 )
15 /
TEXT
--------------------------------------------------------------------------------
ADAMS,JAMES,MILLER,MARTIN,FORD,BLAKE
SQL>
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 06:38:13 CDT 2024
|