发布：2022/12/22 16:45:53作者：管理员 来源：本站 浏览次数：574
IP地址转bigint类型，调用实例：select dbo.fn_get_ip_count('1.13.140.49')
运行结果：
USE [IP]
GO
/****** Object: UserDefinedFunction [dbo].[fn_get_ip_count] Script Date: 2022/12/22 16:41:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fn_get_ip_count]
(
-- Add the parameters for the function here
@ip varchar(50)
)
RETURNS bigint
AS
BEGIN
-- Declare the return variable here
DECLARE @Word varchar(50)
DECLARE @count bigint
DECLARE @i int
set @count=0
set @i=0
DECLARE cursor_name CURSOR FOR --定义游标
SELECT Word FROM dbo.ufn_SplitToTable(@ip,'.') -- and IUIdInt in(40579)
OPEN cursor_name --打开游标
FETCH NEXT FROM cursor_name INTO @Word --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
BEGIN
--print(@PhoneStr)
if len(@Word)>=0
begin
--256 * 256 * 256 * long.Parse(arr[0]) + 256 * 256 * long.Parse(arr[1]) + 256 * long.Parse(arr[2]) + long.Parse(arr[3]);
if @i=0
begin
set @count=@count+256 * 256 * 256 * cast(@Word as decimal(18))
end
else if @i=1
begin
set @count=@count+256 * 256 * cast(@Word as decimal(18))
end
else if @i=2
begin
set @count=@count+256 * cast(@Word as decimal(18))
end
else
begin
set @count=@count+cast(@Word as decimal(18))
end
set @i= @i+1
end
FETCH NEXT FROM cursor_name INTO @Word
END
CLOSE cursor_name --关闭游标
DEALLOCATE cursor_name --释放游标
-- Return the result of the function
RETURN @count
END
GO
USE [IP]
GO/****** Object: UserDefinedFunction [dbo].[ufn_SplitToTable] Script Date: 2022/12/22 16:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--==================================
-- 功能：分隔关键字字串集插入数据表
-- 作者: XXXX
-- 创建: XXXX-XX-XX
-- 修改: XXXX-XX-XX XX XXXXX
-- 调用:SELECT * FROM dbo.ufn_SplitToTable2('1|2|3','|')
--==================================
CREATE FUNCTION [dbo].[ufn_SplitToTable]
(
@chvnKeyWords NVARCHAR(4000), -- 要分隔的关键字字符串集
@chvnSeparator NCHAR(1) -- 要使用的分隔符，默认为'|'
) RETURNS @tblResult TABLE (
Num INT IDENTITY(1, 1) NOT NULL,
Word NVARCHAR(200) NOT NULL
)
--$Encode$--
AS
BEGIN
-- 分隔符参数为NULL时则使用"|"
SET @chvnSeparator = ISNULL(@chvnSeparator, N'|');
DECLARE @intPos AS INT = 0;
SET @intPos = CHARINDEX(@chvnSeparator, @chvnKeyWords);
WHILE @intPos >= 1 /* @intPos > 0*/
BEGIN
IF @intPos >= 2 /*@intPos > 1*/
BEGIN
INSERT INTO @tblResult (Word)
VALUES (LEFT(@chvnKeyWords, @intPos - 1));
END
-- 也可以使用SUBSTRING字符串函数
--SET @chvnKeyWords = SUBSTRING(@chvnKeyWords, @intPos + 1, LEN(@chvnKeyWords) - (@intPos + 1) + 1);
SET @chvnKeyWords = STUFF(@chvnKeyWords, 1, @intPos, N'');
SET @intPos = CHARINDEX(@chvnSeparator, @chvnKeyWords);
END
IF @chvnKeyWords > N''
BEGIN
INSERT INTO @tblResult (Word)
VALUES(@chvnKeyWords);
END
RETURN;
END
GO
调用实例：SELECT Word FROM dbo.ufn_SplitToTable('1.13.140.49','.')
运行结果：
1
13
140
49
