本文介绍如何将GUID格式的IP地址转换为容易阅读的字符串格式。
实现的过程是将IP地址分成4段,每段IP地址再通过数学运算转换为十进制,最后将四段IP地址通过“.”连接起来。
--将GUID格式的IP地址转换为字符串IP地址,如192.168.0.1
CREATE FUNCTION CONVERTIP(@CLIENTIP AS UNIQUEIDENTIFIER)
RETURNS CHAR(255)
BEGIN
declare @IPchar char(255)
DECLARE @IPNUM1 INT
DECLARE @IPNUM2 INT
DECLARE @IPNUM3 INT
DECLARE @IPNUM4 INT
set @IPchar=substring(CONVERT(char(255),@clientIP),1,8)
SET @IPNUM1=(
CASE
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='A' THEN 10*16
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='B' THEN 11*16
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='C' THEN 12*16
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='D' THEN 13*16
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='E' THEN 14*16
WHEN LEFT(SUBSTRING(@IPCHAR,1,2),1)='F' THEN 15*16
ELSE LEFT(SUBSTRING(@IPCHAR,1,2),1)*16
END
+
CASE W