表 Acode(varchar)
0101
0102
0103
0104
010401
010501
01050101写一个update使他们在第四位上全部加1
结果0102
0103
0104
0105
010501
010601
01060101
0101
0102
0103
0104
010401
010501
01050101写一个update使他们在第四位上全部加1
结果0102
0103
0104
0105
010501
010601
01060101
--仅限于第四位小于9
update tb set code=stuff(code,4,1,cast(right(left(code,4),1) as int)+1)
(
SELECT 1 AS aa,'0101' AS Da UNION ALL
SELECT 2 AS aa,'010101' AS Da UNION ALL
SELECT 3 AS aa,'0102' AS Da UNION ALL
SELECT 4 AS aa,'0103' AS Da UNION ALL
SELECT 5 AS aa,'0104' AS Da UNION ALL
SELECT 6 AS aa,'010401' AS Da UNION ALL
SELECT 7 AS aa,'010402' AS Da UNION ALL
SELECT 8 AS aa,'0105' AS Da
)
,cte2 AS
(
SELECT * FROM cte
)
SELECT
cte2.Da,
cast(SUBSTRING(cte.Da,1,3)+ltrim(CAST(SUBSTRING(cte.Da,4,1) AS INT)+1)+SUBSTRING(cte.Da,5,LEN(cte.Da)-4) AS nvarchar(100)) AS New
FROM cte INNER JOIN cte2 ON cte.aa=cte2.aa
/*
Da New
------ -------
0101 0102
010101 010201
0102 0103
0103 0104
0104 0105
010401 010501
010402 010502
0105 0106*/
如果我要大于 大于 0103的呢 0103
如下字段+1。。where 条件怎么写
0104
010401
010501
01050101
update tb set code=stuff(code,4,1,cast(right(left(code,4),1) as int)+1)
where code>'0103'
update tb set code=rtrim(cast(left(code,4) as int)+1)+right(code,len(code)-4)
Go
if not object_ID('Tempdb..#') is null
drop table #
Go
Create table #([Code] varchar(50))
Insert #
select '0101' union all
select '0102' union all
select '0103' union all
select '0104' union all
select '010401' union all
select '010501' union all
select '01050101'
Go
Update # /*注:要是第4位剛好是9,那麼(9+1=10) */
Set Code=Substring(Code,1,3)+Rtrim(substring(Code,4,1)+1)+stuff(Code,1,5,'')go
Select * From # /*
Code
-------------------
0102
0103
0104
0105
01051
01061
0106101
*/
declare @T table (code varchar(8))
insert into @T
select '0101' union all
select '0102' union all
select '0103' union all
select '0104' union all
select '010401' union all
select '010501' union all
select '01050101' union all
select '0109'select code=right('0000'+ltrim(left(code,4)+1),4)
+substring(code,5,len(code)) from @T
/*
code
----------------
0102
0103
0104
0105
010501
010601
01060101
0110
*/