update tb set col=left(col,3)+'0'+right(col,3) where len(col)=6
UPDATE TB SET COL=SUBSTRING(COL,3,LEN(COL)-3,'0'+RIGHT(COL,3)) WHERE LEN(COL)=6
查询出来的结果全变成了ICD0
报错 substring需要三个参数
update table1 set mycol=replcae(mycol,'ICD','ICD0') where len(col)=6
Sorry,应该是 update table1 set mycol=replace(mycol,'ICD','ICD0') where len(col)=6
declare @t table(col varchar(10)) insert @t select 'IDC001' union all select 'IDC999' update @t set col=replace(col,'C','C0')select * from @t
UPDATE TB SET COL=STUFF(COL,3,LEN(COL)-3,'0'+RIGHT(COL,3)) WHERE LEN(COL)=6 写错了个函数
update tb set 字段=left(字段,3)+right('0000'+right(字段,3),4)
update table set col =left(col,3)+'0'+right(col,3)
--测试数据 if OBJECT_ID('tb') is not null drop table tb go CREATE TABLE tb(col varchar(10)) INSERT tb SELECT 'ICD001' UNION ALL SELECT 'ICD002' UNION ALL SELECT 'ICD003' UNION ALL SELECT 'ICD999' GO update tb set col=left(col,3)+'0'+right(col,3) where len(col)=6select * from tb /* col ---------- ICD0001 ICD0002 ICD0003 ICD0999(4 行受影响) */
update tb set col=left(col,3)+'0'+right(col,3) where len(col)=6这个就是可以解决了啊。
set col=left(col,3)+'0'+right(col,3)
where len(col)=6
查询出来的结果全变成了ICD0
报错 substring需要三个参数
update table1 set mycol=replcae(mycol,'ICD','ICD0') where len(col)=6
update table1 set mycol=replace(mycol,'ICD','ICD0') where len(col)=6
declare @t table(col varchar(10))
insert @t
select 'IDC001' union all
select 'IDC999' update @t set col=replace(col,'C','C0')select * from @t
写错了个函数
update tb
set 字段=left(字段,3)+right('0000'+right(字段,3),4)
update table
set col =left(col,3)+'0'+right(col,3)
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(col varchar(10))
INSERT tb
SELECT 'ICD001' UNION ALL
SELECT 'ICD002' UNION ALL
SELECT 'ICD003' UNION ALL
SELECT 'ICD999'
GO update tb
set col=left(col,3)+'0'+right(col,3)
where len(col)=6select * from tb
/*
col
----------
ICD0001
ICD0002
ICD0003
ICD0999(4 行受影响)
*/
update tb
set col=left(col,3)+'0'+right(col,3)
where len(col)=6这个就是可以解决了啊。
set @a='ICD001'
select left(@a,3)+RIGHT('0000'+substring(@a, 4,3),4)
/*
--------------
ICD0001(1 行受影响)*/
set @a='ICD001'
select left(@a,3)+RIGHT('0000'+substring(@a, 4,3),4)
/*
--------------
ICD0001 (1 行受影响)*/
你的问题出在这
要么改varchar
要么select left(@a,3)+'0'+right(@a,17)