一个函数(将ip格式化为010.000.000.001的格式):
CREATE FUNCTION dbo.f_1(@str varchar(8000),@c varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000),@ret varchar(8000)
SET @s=LTRIM(RTRIM(@str))
SET @ret= ' '
WHILE CHARINDEX(@c,@s)> 0
BEGIN
SET @ret=@ret+RIGHT( '000 '+LEFT(@s,CHARINDEX(@c,@s)-1),3)
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
SET @ret=@ret+RIGHT( '000 '+@s,3)
RETURN(@ret)
END
GO
--调用:
select * from table_ip order by dbo.f_1(ip, '. ')