SQL替换字符串、条件查询

1、条件查询1

select (case
         when instr(t.leader, '、') > 0 or instr(t.leader, ';') > 0 or
              instr(t.leader, ',') > 0 then
          regexp_replace(replace(t.leader, '"', ''), ';|、', ',')
         else
          replace(t.leader, ' ', ',')
       end) leader
  from cd_atest t
 where t.leader is not null;

2、条件查询2

select b.leader, b.leader_a, b.leader_original
  from (select a.leader_original,
               a.leader_a,
               (case
                 when instr(a.leader_a, ',') > 0 then
                  SUBSTR(a.leader_a, 0, instr(a.leader_a, ',') - 1)
                 else
                  a.leader_a
               end) leader
          from (select t.leader leader_original,
                       (case
                         when instr(t.leader, '、') > 0 or
                              instr(t.leader, ';') > 0 or
                              instr(t.leader, ',') > 0 then
                          regexp_replace(t.leader, ';|、', ',')
                         when instr(t.leader, '"') > 0 then
                          replace(t.leader, '"', '')
                         else
                          t.leader
                       end) leader_a
                  from cd_atest t
                 where t.leader is not null) a) b
 group by b.leader, b.leader_a, b.leader_original

 

转载于:https://www.cnblogs.com/time-on/p/8378285.html