/ * Written by Jaron, 2003-07-10 * // * First published on 9CBS * / / * Reprint, please indicate the source and keep this copyright information * /
When doing a website statistical expert, we need to count and analyze the IP address segment. Here, the custom function and stored procedure implementation of SQL-Server are directly generated in the database, and the following code can be modified.
/ * SQL statement * /
SELECT TOP 99 DBO.FN_IPLEFT3 (iPaddress), Count (ID) from TBLSTATISTISTIAL GROUP BY DBO.FN_IPLEFT3 (IPADDRESS) Order by Count (ID) DESC
/ * Customize content * /
Create Function Fn_IPLEFT3 (@ipaddr varchar (100)) Returns nvarchar (50) asbegin
Declare @ip_resault varchar (100) set @ ip_resault = ''
Declare @AA varchar (100), @ bb int, @ cc int set @ aa = @ ipaddr
Set @ bb = 0set @ cc = charindex ('.', @ aa) set @ ip_resault = @ ip_resault right ('00' substring (@ aa, @ bb, @ cc- @ bb) '.', 4 )
While @cc> 0begin set @ bb = @ cc 1 set @ cc = charIndex ('.', @ aa, @ bb) set @ ip_resault = @ ip_resault right ('00' substring (@ aa, @ bb, Case when @cc> 0 THEN @ CC- @ BB else Len (@AA) End) '.', 4) End
Return LEFT (@ ip_resault, 11) end
