如果都是数值的话可以用如下方法~~~CREATE TABLE TEST(COL VARCHAR(10),COL2 VARCHAR(10)) INSERT TEST SELECT '503000','' INSERT TEST SELECT '523001','' INSERT TEST SELECT '501100',''BEGIN TRAN UPDATE TEST SET COL2=REVERSE(CAST(REVERSE(COL) AS INT)) SELECT * FROM TEST ROLLBACK TRANDROP TABLE TEST
CREATE TABLE TEST(COL VARCHAR(10),COL2 VARCHAR(10)) INSERT TEST SELECT 'A503000','' INSERT TEST SELECT '5B23001','' INSERT TEST SELECT '501100',''BEGIN TRAN -- 如果都为数值 --UPDATE TEST SET COL2=REVERSE(CAST(REVERSE(COL) AS INT)) SELECT * FROM TEST -- 如果有非数值 UPDATE TEST SET COL2=REPLACE(RTRIM(REPLACE(COL,0,' ')),' ',0) SELECT * FROM TEST ROLLBACK TRANDROP TABLE TEST
declare @str varchar(10) set @str = '555500'select REVERSE(substring(REVERSE(@str),PATINDEX('%[^0]%',REVERSE(@str)),len(@str)))--结果: 5555
INSERT TEST SELECT '503000',''
INSERT TEST SELECT '523001',''
INSERT TEST SELECT '501100',''BEGIN TRAN
UPDATE TEST SET COL2=REVERSE(CAST(REVERSE(COL) AS INT))
SELECT * FROM TEST
ROLLBACK TRANDROP TABLE TEST
insert @a
select 'adfasg0'select replace(r_name,0,'') from @a(所影响的行数为 1 行)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
adfasg(所影响的行数为 1 行)
INSERT TEST SELECT 'A503000',''
INSERT TEST SELECT '5B23001',''
INSERT TEST SELECT '501100',''BEGIN TRAN
-- 如果都为数值
--UPDATE TEST SET COL2=REVERSE(CAST(REVERSE(COL) AS INT))
SELECT * FROM TEST -- 如果有非数值
UPDATE TEST SET COL2=REPLACE(RTRIM(REPLACE(COL,0,' ')),' ',0)
SELECT * FROM TEST
ROLLBACK TRANDROP TABLE TEST
declare @str varchar(10)
set @str = '555500'select REVERSE(substring(REVERSE(@str),PATINDEX('%[^0]%',REVERSE(@str)),len(@str)))--结果:
5555