Using SQL statements to count the first three paragraphs of an IP address

IP Address | statistics | statement





/* Written by Jaron, 2003-07-10 * *


/* First published in CSDN * * *


/* Reprint Please specify the source and retain this copyright information * *








in doing site statistics experts, we need to statistics and analysis of IP address segment, where the application of Sql-server custom functions and stored procedures to achieve direct results in the database, the following code slightly modified.





/* SQL statement */





Select Top Dbo.fn_ipleft3 (IPAddress), Count (IDs) from Tblstatisticaldetail Group by DBO.FN_IPLEFT3 (IPAddress) Orde R by count (ID) desc








/* Custom Function content * *





CREATE function fn_ipleft3 (@ipaddr varchar (100))


returns nvarchar (50)


as


begin





declare @ip_resault varchar (100)


Set @ip_resault = '





declare @aa varchar (m), @bb int, @cc int


Set @aa = @ipaddr





Set @bb = 0


set @cc =charindex ('. ', @aa)


Set @ip_resault = @ip_resault +right (@aa, @bb, @cc-@bb) + '. ', 4





while @cc >0


begin


Set @bb = @cc +1


set @cc =charindex ('. ', @aa, @bb)


Set @ip_resault = @ip_resault +right (@aa, @bb, case when @cc >0 then @cc-@bb Else Len (@aa) end) + '. ', 4


End





return Left (@ip_resault, 11)


End