表TB,有列CGI(数据类型为NVARCHAR(255))
现数值如下
1233-11
1897-632
1744-6991
3255-54123想在原表中改为
1233-00011
1897-00632
1744-06991
3255-54123上述要求用下面的语句已能实现。
create table tb(CGI NVARCHAR(255))
insert into tb values('1233-11')
insert into tb values('1897-632')
insert into tb values('1744-6991')
insert into tb values('3255-54123')
go
--查询
select cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5) from tb
/*
cgi
----------------------
1233-00011
1897-00632
1744-06991
3255-54123(所影响的行数为 4 行)
*/--更新
update tb set cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5)
select * from tb
--查询
select cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5) from tb
/*
cgi
----------------------
1233-00011
1897-00632
1744-06991
3255-54123(所影响的行数为 4 行)
*/drop table tb************************
现想逆向操作,撒销刚才补上的0,还原为初始状态,求语句。谢谢!
现数值如下
1233-11
1897-632
1744-6991
3255-54123想在原表中改为
1233-00011
1897-00632
1744-06991
3255-54123上述要求用下面的语句已能实现。
create table tb(CGI NVARCHAR(255))
insert into tb values('1233-11')
insert into tb values('1897-632')
insert into tb values('1744-6991')
insert into tb values('3255-54123')
go
--查询
select cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5) from tb
/*
cgi
----------------------
1233-00011
1897-00632
1744-06991
3255-54123(所影响的行数为 4 行)
*/--更新
update tb set cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5)
select * from tb
--查询
select cgi = left(cgi,charindex('-',cgi)) + right('00000'+substring(cgi,charindex('-',cgi)+1,len(cgi)),5) from tb
/*
cgi
----------------------
1233-00011
1897-00632
1744-06991
3255-54123(所影响的行数为 4 行)
*/drop table tb************************
现想逆向操作,撒销刚才补上的0,还原为初始状态,求语句。谢谢!
--添加0用replicate就可以了
update tb set cgi=substring(cgi,0,charindex('-',cgi))+replace(substring(cgi,charindex('-',cgi),len(cgi)),'0','')
-->TravyLee生成测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(20))
insert [test]
select '1233-11' union all
select '1897-632' union all
select '1744-6991' union all
select '3255-54123'
update test
set value=LEFT(value,CHARINDEX('-',value))
+right('00000'+RIGHT(value,len(value)-CHARINDEX('-',value)),5)/*
value
---------------
1233-00011
1897-00632
1744-06991
3255-54123
*/
update test
set value=LEFT(value,CHARINDEX('-',value))+LTRIM(CAST(right(value,5) as int)) select * from test/*
value
1233-11
1897-632
1744-6991
3255-54123
*/
rtrim(substring(cgi,charindex('-',cgi)+1,len(cgi)*1)
-->TravyLee生成测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test]([value] varchar(20))
insert [test]
select '1233-11' union all
select '1897-632' union all
select '1744-6991' union all
select '3255-54123'
update test
set value=LEFT(value,CHARINDEX('-',value))
+right('00000'+RIGHT(value,len(value)-CHARINDEX('-',value)),5)/*
value
---------------
1233-00011
1897-00632
1744-06991
3255-54123
*/
update test
set value=LEFT(value,CHARINDEX('-',value))+LTRIM(CAST(right(value,5) as int)) --如果右边的不固定update test
set value=LEFT(value,CHARINDEX('-',value))+
LTRIM(cast(right(value,len(value)-CHARINDEX('-',value)) as int))
select * from test/*
value
1233-11
1897-632
1744-6991
3255-54123
*/
CREATE TABLE t1
(
cgi VARCHAR(255)
)
INSERT INTO t1
SELECT '1233-00011' UNION all
SELECT '1897-00632' UNION all
SELECT '1744-06991' UNION all
SELECT '3255-54123'
SELECT * FROM t1SELECT REPLACE(cgi,'0','') FROM t1------------------------
(无列名)
1233-11
1897-632
1744-6991
3255-54123