求一条oracle替换字符串的语句

Question

比如users表中
loginname    description 
aaaa            2222
我想把description 中 第三个2改成3 
请问用什么语句呢 
我写的是update users set description='3'||substr(description,-2)where loginname='aaaa'我是新手 请大家帮我解释一下这个的详细意思

Answer 1

update users
set description=2232
where loginname='aaaa'
/

Answer 2

'3'||substr(description,-2)第一个字符是3，拼接原来description除第3个位置的2以外其他的字符，应该是这意思吧
这个前提应该是description必须长度是4吧？
我没用过

Answer 3

你好 又见到你了~
不是这个语句 这个我知道
我想通过一条语句 只换指定位数的值 好像是用substr这个
帮帮忙 谢谢啦

Answer 4

-- 1) 如果description字段是字符类型：
update users
set description=substr(description,1,instr(description,'2',1,3)-1)||
                              substr(description,instr(description,'2',1,3)+1)
where loginname='aaaa';-- 2) 如果description字段是数值类型（是整型)：
update users
set description=substr(to_char(description),1,instr(to_char(description),'2',1,3)-1)||
                              substr(to_char(description),instr(to_char(description),'2',1,3)+1)
where loginname='aaaa';

Answer 5

idle> conn scott/bee56915
已连接。
scott@SZTYORA> create table users(
  2    loginname varchar2(20),
  3    description1 varchar2(20),  -- varchar2类型
  4    description2 number(18,0),  -- 整型(无小数)
  5    description3 number(18,4)   -- 浮点型(有4位小数)
  6  );
create public synonym USERS for scott.USERS表已创建。scott@SZTYORA>
scott@SZTYORA> insert into users(loginname,description1,description2,description3) values('aaaa','2222',2222,2222.222);已创建 1 行。scott@SZTYORA> insert into users(loginname,description1,description2,description3) values('bbbb','22324',22324,22324.2232);已创建 1 行。scott@SZTYORA>
scott@SZTYORA> commit;提交完成。scott@SZTYORA> select * from users;LOGINNAME                                DESCRIPTION1                             DESCRIPTION2 DESCRIPTION3
---------------------------------------- ---------------------------------------- ------------ ------------
aaaa                                     2222                                             2222     2222.222
bbbb                                     22324                                           22324   22324.2232scott@SZTYORA> update users
  2  set description1=substr(description1,1,instr(description1,'2',1,3)-1)||
  3                                substr(description1,instr(description1,'2',1,3)+1)
  4  where loginname='aaaa';已更新 1 行。scott@SZTYORA> select * from users;LOGINNAME                                DESCRIPTION1                             DESCRIPTION2 DESCRIPTION3
---------------------------------------- ---------------------------------------- ------------ ------------
aaaa                                     222                                              2222     2222.222
bbbb                                     22324                                           22324   22324.2232scott@SZTYORA> rollback;回退已完成。scott@SZTYORA> select * from users;LOGINNAME                                DESCRIPTION1                             DESCRIPTION2 DESCRIPTION3
---------------------------------------- ---------------------------------------- ------------ ------------
aaaa                                     2222                                             2222     2222.222
bbbb                                     22324                                           22324   22324.2232scott@SZTYORA> update users
  2  set description1=substr(description1,1,instr(description1,'2',1,3)-1)||'3'||
  3                                substr(description1,instr(description1,'2',1,3)+1)
  4  where loginname='aaaa';已更新 1 行。scott@SZTYORA> select * from users;LOGINNAME                                DESCRIPTION1                             DESCRIPTION2 DESCRIPTION3
---------------------------------------- ---------------------------------------- ------------ ------------
aaaa                                     2232                                             2222     2222.222
bbbb                                     22324                                           22324   22324.2232scott@SZTYORA> update users
  2  set description2=substr(to_char(description2),1,instr(to_char(description2),'2',1,3)-1)||'3'||
  3                                substr(to_char(description2),instr(to_char(description2),'2',1,3)+1)
  4  where loginname='aaaa';已更新 1 行。scott@SZTYORA> select * from users;LOGINNAME                                DESCRIPTION1                             DESCRIPTION2 DESCRIPTION3
---------------------------------------- ---------------------------------------- ------------ ------------
aaaa                                     2232                                             2232     2222.222
bbbb                                     22324                                           22324   22324.2232scott@SZTYORA>

Answer 6

-- 1) 如果description字段是字符类型：
update users
set description1=substr(description1,1,instr(description1,'2',1,3)-1)||'3'||
                              substr(description1,instr(description1,'2',1,3)+1)
where loginname='aaaa';-- 2) 如果description字段是数值类型（是整型)：
update users
set description2=substr(to_char(description2),1,instr(to_char(description2),'2',1,3)-1)||'3'||
                              substr(to_char(description2),instr(to_char(description2),'2',1,3)+1)
where loginname='aaaa';

Answer 7

-- 1) 如果description字段是字符类型：
update users
set description1=substr(description1,1,instr(description1,'2',1,3)-1)||'3'||
                              substr(description1,instr(description1,'2',1,3)+1)
where loginname='aaaa'
  and length(description1)-length(replace(description1,'2','')>2; 
-- 最好加个条件：只有在description1出现三个或三个以上的“2”时才更新！-- 2) 如果description字段是数值类型（是整型)：
update users
set description2=substr(to_char(description2),1,instr(to_char(description2),'2',1,3)-1)||'3'||
                              substr(to_char(description2),instr(to_char(description2),'2',1,3)+1)
where loginname='aaaa'
  and length(to_char(description2))-length(replace(to_char(description2),'2','')>2; 
-- 最好加个条件：只有在description1出现三个或三个以上的“2”时才更新！

Answer 8

--用正则表达式吧，只要你的密码全为数字，那么将所有的第三个数字替换为3
SQL> with t as(
  2       select 'aaa' c1,'222222' c2 from dual union all
  3       select 'ddafdf','555555' from dual union all
  4       select 'eeeeee','111111' from dual union all
  5       select 'fff','000000' from dual)
  6  select c1,regexp_replace(c2,'[[:digit:]]','3',1,3) c2 from t
  7  /
 
C1     C2
------ --------------------------------------------------------------------------------
aaa    223222
ddafdf 553555
eeeeee 113111
fff    003000

Answer 9

--第三位是字母，数字都无所有了，只要你的oracle版本在10g以上
SQL> with t as(
  2       select 'aaa' c1,'222222' c2 from dual union all
  3       select 'ddafdf','555555' from dual union all
  4       select 'eeeeee','111111' from dual union all
  5       select 'fff','aaaaa' from dual)
  6  select c1,regexp_replace(c2,'([[:digit:]]|[[:alpha:]])','3',1,3) c2 from t
  7  /
 
C1     C2
------ --------------------------------------------------------------------------------
aaa    223222
ddafdf 553555
eeeeee 113111
fff    aa3aa

Answer 10

谢谢 成功了 
能解释一下原理吗 我只知道
substr（description1,1,3）是从字段第一位置开始截取3个字符
instr(description1,'2',1,3）是从字段中第一位置搜索2 搜索3次

Answer 11

-- 当然能啦，把select语句变成update语句，不会说：你不会吧！？

Answer 12

create table t as
     select 'aaa' c1,'222222' c2 from dual union all
     select 'ddafdf','555555' from dual union all
     select 'eeeeee','111111' from dual union all
     select 'fff','aaaaa' from dual
SQL> update t
  2  set c2=(
  3      select regexp_replace(c2,'([[:digit:]]|[[:alpha:]])','3',1,3)
  4      from t
  5      where c1='aaa'
  6      )
  7  where c1='aaa'
  8  /
 
1 row updated
SQL> update t
  2  set c2=(
  3      select regexp_replace(c2,'([[:digit:]]|[[:alpha:]])','3',1,3)
  4      from t
  5      where c1='fff'
  6      )
  7  where c1='fff'
  8  /
 
1 row updated
 
SQL> select * from t;
 
C1     C2
------ ------
aaa    223222
ddafdf 555555
eeeeee 111111
fff    aa3aa

Answer 13

哈哈 我成功了 谢谢你们 我稍微更改了一下到我可以理解的范围哈哈update users set description=regexp_replace(description,'[[:digit:]]','3',1,3) where loginname = 'pszyywhj'update users set description=substr(description,1,2)||'3'||substr(description,4,1)where loginname='pszyywhj';只可惜分太少了 不然多给你们分