请熟悉数据库的都进来看看！

Question

大家好：
    数据库中某表中有字段tcardid,比如有值5101000100125010220100311001，
    其中510100（区域号）01001250102（流水号）20100311（时间）001（序号），现在要改变这个值，
    把流水号中的前两位‘01’去掉，变为‘001250102’， 在'序号'之前加上两个字符‘00’，变为
    '00001',最后更新为
       510100 001250102 20100311 00001
    由于tcardid值很多，手改不行，想问问能不能用sql语句解决？谢谢各位了！

Answer 1

 
update tb set tcadid=stuff(tcadid,7,2,'')
update tb set tcadid=stuff(tcadid,23,1,'000')

Answer 2

Select cast(SubString(tcardid,1,7) as varchar(7)) + cast(SubString(tcardid,10,17) as varchar(17)) + '00' + cast(SubString(tcardid,18,3) as varchar(3)) From ...

Answer 3

declare @tcadid nvarchar(100)
select @tcadid='5101000100125010220100311001'
select @tcadid
select @tcadid=stuff(@tcadid,7,2,'')
select @tcadid
select @tcadid=stuff(@tcadid,23,1,'000')
select @tcadid                                                                                                     
---------------------------------------------------------------------------------------------------- 
5101000100125010220100311001（所影响的行数为 1 行）                                                                                                     
---------------------------------------------------------------------------------------------------- 
51010000125010220100311001（所影响的行数为 1 行）                                                                                                     
---------------------------------------------------------------------------------------------------- 
5101000012501022010031000001（所影响的行数为 1 行）

Answer 4

好像错了 第二个改成24update tb set tcadid=stuff(tcadid,7,2,'')
update tb set tcadid=stuff(tcadid,24,1,'000')
declare @tcadid nvarchar(100)
select @tcadid='5101000100125010220100311001'
select @tcadid
select @tcadid=stuff(@tcadid,7,2,'')
select @tcadid
select @tcadid=stuff(@tcadid,24,1,'000')
select @tcadid                                                                                                     
                                                                                                     
---------------------------------------------------------------------------------------------------- 
5101000100125010220100311001（所影响的行数为 1 行）                                                                                                     
---------------------------------------------------------------------------------------------------- 
51010000125010220100311001（所影响的行数为 1 行）                                                                                                     
---------------------------------------------------------------------------------------------------- 
5101000012501022010031100001（所影响的行数为 1 行）

Answer 5

update 表
Set tcardid = stuff(stuff(tcadid,7,2,''),23,1,'000')

Answer 6

现在对了 就用8楼的update tb set tcadid=stuff(tcadid,7,2,'')
update tb set tcadid=stuff(tcadid,24,1,'000')

Answer 7

或者直接update tb set tcadid=stuff(stuff(tcadid,7,2,''),22,1,'000')

Answer 8

stuff的功能：删除指定长度的字符串并在指定的起始点插入另一组字符
如SELECT STUFF('abcdef', 2, 3, '123456')
a123456ef

Answer 9

update tablename set tcardid=left(tcardid,6)+substring(tcardid,9,17)+'00'+right(tcardid,3)

Answer 10

update tb set tcadid=stuff(stuff(tcadid,7,2,''),22,1,'000')