问大家一下,sql字符串替换的问题,谢谢!

Question

Oracle自定义函数:
v_str := '1,2,3,4,5,6...'
....现在是一共有5个',',希望把奇数位置上的','替换成'|',
不知道有没有什么好办法 谢谢大家

Answer 1

-- 可以用正则表达式，也可以用下面的10g方法:
SQL> SELECT '1,2,3,4,7,12,13' STR FROM TABLE_NAME;STR
---------------
1,2,3,4,7,12,13
SQL> SELECT RTRIM(REPLACE(REPLACE(WMSYS.WM_CONCAT(DECODE(ABS(MOD(SUBSTR(',' || STR || ',',
  2                                                                     INSTR(',' || STR || ',',
  3                                                                           ',',
  4                                                                           1,
  5                                                                           RN) + 1,
  6                                                                     INSTR(',' || STR || ',',
  7                                                                           ',',
  8                                                                           1,
  9                                                                           RN + 1) -
 10                                                                     INSTR(',' || STR || ',',
 11                                                                           ',',
 12                                                                           1,
 13                                                                           RN) - 1),
 14                                                              2)),
 15                                                      1,
 16                                                      SUBSTR(',' || STR || ',',
 17                                                             INSTR(',' || STR || ',',
 18                                                                   ',',
 19                                                                   1,
 20                                                                   RN) + 1,
 21                                                             INSTR(',' || STR || ',',
 22                                                                   ',',
 23                                                                   1,
 24                                                                   RN + 1) -
 25                                                             INSTR(',' || STR || ',',
 26                                                                   ',',
 27                                                                   1,
 28                                                                   RN) - 1) || '|',
 29                                                      SUBSTR(',' || STR || ',',
 30                                                             INSTR(',' || STR || ',',
 31                                                                   ',',
 32                                                                   1,
 33                                                                   RN) + 1,
 34                                                             INSTR(',' || STR || ',',
 35                                                                   ',',
 36                                                                   1,
 37                                                                   RN + 1) -
 38                                                             INSTR(',' || STR || ',',
 39                                                                   ',',
 40                                                                   1,
 41                                                                   RN) - 1) || ',')),
 42                               '|,',
 43                               '|'),
 44                       ',,',
 45                       ','),
 46               ',|') YOUR_STR
 47    FROM TABLE_NAME TT,
 48         (SELECT ROWNUM RN
 49            FROM DUAL
 50          CONNECT BY ROWNUM <=
 51                     (SELECT LENGTHB(STR) - LENGTHB(REPLACE(STR, ',', ''))
 52                        FROM TABLE_NAME TT) + 1) BB
 53   WHERE INSTR(',' || STR || ',', ',', 1, RN) > 0;YOUR_STR
--------------------------------------------------------------------------------
1|2,3|4,7|12,13

Answer 2

想写成函数的话,这样做:
SQL> declare
  2    v_str     varchar2(100);
  3    v_new_str varchar2(100);
  4    v_count   int;
  5  begin
  6    v_str     := '1,2,3,4,5,67,8,9';
  7    v_count   := 0;
  8    v_new_str := '';
  9    for i in 1 .. length(v_str) loop
 10      if substr(v_str, i, 1) = ',' then
 11        v_count := v_count + 1;
 12      end if;
 13      if substr(v_str, i, 1) = ',' and mod(v_count, 2) = 1 then
 14        v_new_str := v_new_str || '|';
 15      else
 16        v_new_str := v_new_str || substr(v_str, i, 1);
 17      end if;
 18    end loop;
 19    dbms_output.put_line(v_new_str);
 20  end;
 21  /1|2,3|4,5|67,8|9PL/SQL procedure successfully completedSQL> 

Answer 3

是啊，要sleepzzzzz想我了真不好意思哟，嘿嘿