Home » SQL & PL/SQL » SQL & PL/SQL » Merge String (Oracle 11g)
Merge String [message #641659] Thu, 20 August 2015 01:18 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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>
Re: Merge String [message #641663 is a reply to message #641661] Thu, 20 August 2015 02:02 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks Lalit.. Can we use regular expression for the same ?
Re: Merge String [message #641664 is a reply to message #641663] Thu, 20 August 2015 02:12 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes you can. By the way I used regexp to first split into rows. You could use REGEXP_REPLACE after LISTAGG without splitting into rows.

[Updated on: Thu, 20 August 2015 02:12]

Report message to a moderator

Previous Topic: How to implement Materialized view in PL/SQL using Collections
Next Topic: PL/SQL: ORA-00911: invalid character
Goto Forum:
  


Current Time: Thu Mar 28 06:38:13 CDT 2024