Merge of two strings in Oracle SQL

Question

Is there any way to merge two strings returned in a query like this: I have one string '<6 spaces>XYZ' and other string '<3 spaces>ABC<3 spaces>'. Basically each string is divided in 3 parts and two of any parts will be blank. I want to merge these two strings to produce the output: '<3 spaces> ABCXYZ'.

Another example can be 'ABC<6 spaces>' and string '<6 spaces>DEF' should produce 'ABC<3 spaces>DEF>'

Answer 1

Oracle 10g+:

SELECT t.column1,
       t.column2,
       CASE
         WHEN REGEXP_LIKE(t.column1, '^\w{3}') AND REGEXP_LIKE(t.column2, '\w{3}$') THEN 
           TRIM(t.column1) || '   ' || TRIM(t.column2)            
         WHEN REGEXP_LIKE(t.column1, '^\w{3}') AND REGEXP_LIKE(t.column2, '\s{3}\w{3}') THEN 
           TRIM(t.column1) || TRIM(LEADING FROM t.column2) 
         WHEN REGEXP_LIKE(t.column1, '\s{3}\w{3}') AND REGEXP_LIKE(t.column2, '\w{3}$') THEN 
           TRIM(TRAILING FROM t.column1) || TRIM(t.column2) 
         WHEN REGEXP_LIKE(t.column2, '^\w{3}') AND REGEXP_LIKE(t.column1, '\w{3}$') THEN 
           TRIM(t.column2) || '   ' || TRIM(t.column1)            
         WHEN REGEXP_LIKE(t.column2, '^\w{3}') AND REGEXP_LIKE(t.column1, '\s{3}\w{3}') THEN 
           TRIM(t.column2) || TRIM(LEADING FROM t.column1) 
         WHEN REGEXP_LIKE(t.column2, '\s{3}\w{3}') AND REGEXP_LIKE(t.column1, '\w{3}$') THEN 
           TRIM(TRAILING FROM t.column2) || TRIM(t.column1)   
       END AS col
  FROM table1 t 

Supporting scripts:

CREATE TABLE "EXAMPLE"."TABLE1" (
  "COLUMN1" VARCHAR2(9 BYTE), 
  "COLUMN2" VARCHAR2(9 BYTE)
);

Insert into TABLE1 (COLUMN1,COLUMN2) values ('   ABC   ','      DEF');
Insert into TABLE1 (COLUMN1,COLUMN2) values ('ABC      ','      DEF');
Insert into TABLE1 (COLUMN1,COLUMN2) values ('   DEF   ','ABC      ');
Insert into TABLE1 (COLUMN1,COLUMN2) values ('      DEF','   ABC   ');
Insert into TABLE1 (COLUMN1,COLUMN2) values ('ABC      ','   DEF   ');
Insert into TABLE1 (COLUMN1,COLUMN2) values ('      DEF','ABC      ');

Answer 2

You can take advantage of the fact that spaces are alphabetically earlier than letters and numbers:

with q as (select '   ABC   ' as c1,'      DEF' as c2 from dual
 union all select 'ABC      ', '      DEF' from dual
 union all select '   DEF   ', 'ABC      ' from dual
 union all select '      DEF', '   ABC   ' from dual
 union all select 'ABC      ', '   DEF   ' from dual
 union all select '      DEF', 'ABC      ' from dual
)
select c1, c2,
       GREATEST(SUBSTR(c1, 1, 3), SUBSTR(c2, 1, 3)) ||
       GREATEST(SUBSTR(c1, 4, 3), SUBSTR(c2, 4, 3)) ||
       GREATEST(SUBSTR(c1, 7, 3), SUBSTR(c2, 7, 3)) mrg
from q;


C1        C2        MRG       
--------- --------- --------- 
   ABC          DEF    ABCDEF 
ABC             DEF ABC   DEF 
   DEF    ABC       ABCDEF    
      DEF    ABC       ABCDEF 
ABC          DEF    ABCDEF    
      DEF ABC       ABC   DEF 

6 rows selected