SQL如何批量替换字符？

[huang_w]

表名 a
字段name
字段里面的值有如：v_abc_type
替换成vAbcType
就是把name字段中的值带下划线的都变成java驼峰格式的组合，下划线去掉并将下划线右边的字母变成大写
高手来帮忙一下
谢谢~

[huang_w]

引用 1 楼 lu010610 的回复:

给你写个转换函数

create or replace function ff_lwb (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
    select ff_lwb(v_start) into v_endstr from dual;
  end if;
  return v_endstr;
exception
  when others then
    return in_str;
end;

这样应该就会了吧

真给力！！

[_拙计]

给你写个转换函数

create or replace function ff_lwb (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
    select ff_lwb(v_start) into v_endstr from dual;
  end if;
  return v_endstr;
exception
  when others then
    return in_str;
end;

这样应该就会了吧

[snbxp]

SELECT REPLACE(NLS_INITCAP('v_abc_type'),'_') FROM DUAL

[一颗向往数据的心丶]

引用 1 楼 lu010610 的回复:

给你写个转换函数

create or replace function ff_lwb (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
    select ff_lwb(v_start) into v_endstr from dual;
  end if;
  return v_endstr;
exception
  when others then
    return in_str;
end;

这样应该就会了吧

这个不错...但是你的循环为什么是function调用循环？为啥部内部Loop循环：

create or replace function ff_lwb2 (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  loop
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
    exit;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
  end if;
  end loop;
  return v_endstr;
exception
  when others then
    return in_str;
end;

[_拙计]

引用 3 楼 z_shousi 的回复:

[quote=引用 1 楼 lu010610 的回复:] 给你写个转换函数

create or replace function ff_lwb (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
    select ff_lwb(v_start) into v_endstr from dual;
  end if;
  return v_endstr;
exception
  when others then
    return in_str;
end;

这样应该就会了吧

这个不错...但是你的循环为什么是function调用循环？为啥部内部Loop循环：

create or replace function ff_lwb2 (in_str varchar2)--将字符串转为驼峰格式
return varchar2 is
  v_start  varchar2(100);
  v_endstr varchar2(100);
begin
  v_start := in_str;
  loop
  if instr(v_start, '_') = 0 then
    v_endstr := v_start;
    exit;
  else
    select replace(v_start,
                   substr(v_start, instr(v_start, '_'), 2),
                   upper(substr(v_start, instr(v_start, '_') + 1, 1)))--upper将小写转为大写
      into v_endstr
      from dual;
    v_start := v_endstr;
  end if;
  end loop;
  return v_endstr;
exception
  when others then
    return in_str;
end;

[/quote]java程序写多了