sql - 在 Oracle SQL 中合并两个字符串

Question

有什么方法可以像这样合并查询中返回的两个字符串:我有一个字符串 '<6 spaces>XYZ'和其他字符串 '<3 spaces>ABC<3 spaces>' .基本上每个字符串分为 3 个部分，任何部分中的两个都是空白的。我想合并这两个字符串以生成输出:'<3 spaces> ABCXYZ' .

另一个例子可以是'ABC<6 spaces>'和字符串 '<6 spaces>DEF'应该产生 'ABC<3 spaces>DEF>'

Answer 1

您可以利用空格按字母顺序早于字母和数字这一事实:

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