如我有个表,表中有如下字段
ipstart ipend 4.22.36.58 4.22.39.80
我想把每个段(用.隔开的数字更新成3位数)如4.22.36.58 → 004.022.036.058
4.22.39.80 → 004.022.039.080
请问用存储过程该如何实现?
ipstart ipend 4.22.36.58 4.22.39.80
我想把每个段(用.隔开的数字更新成3位数)如4.22.36.58 → 004.022.036.058
4.22.39.80 → 004.022.039.080
请问用存储过程该如何实现?
as
declare @str varchar(100)
set @str=''
declare @i int
select @i=charindex('.',@IP)
while @i > 0
begin
set @str=@str+right('00'+(left(@IP,@i-1)),3)+'.'
select @IP=substring(@IP,@i+1,20)
select @i=charindex('.',@IP)
end
select @str+right('00'+@IP,3)
go
exec up_getip '4.22.36.58'/*
-----------------------
004.022.036.058(所影响的行数为 1 行)
*/
insert #tmp(a) select top 100 '' as y from sysobjectsdeclare @a table(a varchar(100),b varchar(100))
declare @b varchar(100)
set @b=''
insert @a select '4.22.36.58',''
select
id,right('00'+ substring(a.a,b.id,charindex('.',a.a+'.',b.id)-b.id),3) gg into #t
from #tmp b,@a a
where substring('.'+a.a,b.id,1)='.'
select @b=@b+gg+'.' from #t
update @a set b=left(@b,len(@b)-1)
select * from @a
drop table #tmp,#t
--result
a b
------------------------------ ------------------------------
4.22.36.58 004.022.036.058
update table_Pqs set IP=dbo.up_GetIP(IP)就可以
returns varchar(100) as
declare @str varchar(100)
set @str=''
declare @i int
select @i=charindex('.',@IP)
while @i > 0
begin
set @str=@str+right('00'+(left(@IP,@i-1)),3)+'.'
select @IP=substring(@IP,@i+1,20)
select @i=charindex('.',@IP)
end
RETURN( @str+right('00'+@IP,3))
go
这个好像不可以
returns varchar(100) as
begin
declare @str varchar(100)
set @str=''
declare @i int
select @i=charindex('.',@IP)
while @i > 0
begin
set @str=@str+right('00'+(left(@IP,@i-1)),3)+'.'
select @IP=substring(@IP,@i+1,20)
select @i=charindex('.',@IP)
end
RETURN( @str+right('00'+@IP,3))
end
go加上begin 和 end就好了,分少了点,大家见谅!