Copy the codeThe code is as follows:
-- Establish IP conversion method
USE [BasName]
GO
/****** Object: UserDefinedFunction [dbo].[X16ToDe] Script Date: 09/19/2007 13:56:15 *******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
--Description: Convert IP to decimal
-- =============================================
Create FUNCTION [dbo].[X16ToDe]
(
@Old_IP nvarchar(15)
)
RETURNS numeric
AS
BEGIN
DECLARE
@CharIndex INT,
@CurrPoint INT,
@SingleValue NVARCHAR(5),
@Cache numeric
SET @CharIndex = 1
SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)
SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)
SET @Cache = cast(@SingleValue as numeric)*16777216
SET @CharIndex = @CurrPoint + 1
SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)
SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)
SET @Cache = @Cache + cast(@SingleValue as numeric)*65536
SET @CharIndex = @CurrPoint + 1
SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)
SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)
SET @Cache = @Cache + cast(@SingleValue as numeric)*256
SET @CharIndex = @CurrPoint + 1
SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,len(@Old_IP)- @CharIndex + 1)
SET @Cache = @Cache + cast(@SingleValue as numeric)
RETURN @Cache;
END
Copy the codeThe code is as follows:
-- Create a new decimal table
USE [BasName]
GO
/****** Object: Table [dbo].[IP_Real] Script Date: 09/19/2007 14:01:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TABLE [dbo].[IP_Real](
[startip] [numeric](18, 0) NULL,
[endip] [numeric](18, 0) NULL,
[country] [nvarchar](50) NULL,
[local] [nvarchar](200) NULL
) ON [PRIMARY]
You can do this step by yourself according to your situation. I am converting all the IPs into decimal and saving them into a new table.
-- Format provinces
Copy the codeThe code is as follows:
Update [BasName].[dbo].[IP]
SET [country] = replace([country],N'Province',N'Province ')
-- delete
Copy the codeThe code is as follows:
Update [BasName].[dbo].[IP]
SET [country] = replace([country],N'',N'')
-- Propose the region
Update [BasName].[dbo].[IP]
SET [local] = SUBSTRING([country],CHARINDEX(' ',[country],1)+1,len([country]))
--Save as a country or province
Update [BasName].[dbo].[IP]
SET [country] = SUBSTRING([country],0,CHARINDEX(' ',[country],1))
-- The spaces leading to the front and back of the place
Update [BasName].[dbo].[IP]
SET [country] = Rtrim(Ltrim([country]))
,[local] = Rtrim(Ltrim([local]))
-- Convert IP to decimal and write to a new table
Insert INTO [BasName].[dbo].[IP_Real]
([startip]
,[endip]
,[country]
,[local])
Select dbo.X16ToDe([startip])
,dbo.X16ToDe([endip])
,[country]
,[local]
FROM [BasName].[dbo].[IP]
order by [startip] ASC
Finally, let's take a look:
Program code
-- test
DECLARE @IPNumber numeric
set @IPNumber = dbo.X16ToDe('219.140.31.91')
Select [startip]
,[endip]
,[country]
,[local]
FROM [BasName].[dbo].[IP_Real]
Where [startip] <= @IPNumber and [endip] >= @IPNumber
Basically all conversions can be completed in up to 3 minutes, which is much faster than writing the application conversion yourself.