34,576
社区成员
发帖
与我相关
我的任务
分享
declare @s varchar(100)='000000612'
select case when convert(bigint,@s)>10000 then convert(varchar,(convert(bigint,@s))) else right(@s,4) end
update userinfo set badid=RIGHT('0000'+substring(badgenumber,patindex('%[^0]%',badgenumber),15),
case when LEN(badgenumber)-patindex('%[^0]%',badgenumber)+1<4
then 4 else LEN(badgenumber)-patindex('%[^0]%',badgenumber)+1 end)
SELECT ISNULL(REPLICATE('0',
4 - LEN(SUBSTRING(s, PATINDEX('%[^0]%', s), LEN(s)))),
'') + SUBSTRING(s, PATINDEX('%[^0]%', s), LEN(s))
FROM ( SELECT '000004238' s
UNION ALL
SELECT '000684291'
UNION ALL
SELECT '012534289'
UNION ALL
SELECT '000000012'
) t;
update userinfo set badid=right('0000'+substring(patindex('%[^0]%',badgenumber),15),4)
update userinfo set badid=right('0000'+substring(badgenumber,patindex('%[^0]%',badgenumber),15),4)
update userinfo set badid=right(badgenumber,4)