Convert pure IP database | qq ip database into SQL, access

Reprinted from: http://www.cnblogs.com/efreer/archive/2008/12/12/1353410.html
• Newest coral worm IP database http://update.cz88.net/soft/qqwry.rar

• Microsoft Office Access 2003
• Eiditplus
• MS sql2005

1. Open showip.exe, which is included in the IP address database of the coral worm, and extract it. save another TXT file:

2. Use eiditplus to open the text and drag it to the last few lines to delete unnecessary items (do not try to open it in the default text editor. If the memory is small, you will die badly ):

3. Open Access (why not import data directly into SQL? Because the text format is incorrect, you can save the country first ):

4. Select the extracted text file:

5. follow the steps below to proceed directly without prompt:



7. Open Ms
Sql2005: Create a new database with its own name. The following code uses [basname] to replace your new database name.
8. Continue with the following operations:


Complete the following stored procedure:

-- Create an IP address conversion to decimal
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 the IP address 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

In this step, you can follow your own situation. I want to speed up database indexing, so I want to convert all IP addresses to decimal and store them in a new table.

-- Create a new decimal table
Use [basname]
Go
/***** Object: Table [DBO]. [ip_real]
Script Date: 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]

-- Format the province
Update [basname]. [DBO]. [IP]
Set [country] =
Replace ([country], n' province ', n' province ')
-- Delete cz88.net
Update
[Basname]. [DBO]. [IP]
Set [country] = Replace ([country], n'cz88. net', n '')
--
Propose regions
Update [basname]. [DBO]. [IP]
Set [local] =
Substring ([country], charindex ('', [country], 1) + 1, Len ([country])
--
Saved as country or province
Update [basname]. [DBO]. [IP]
Set [country] =
Substring ([country], 0, charindex ('', [country], 1 ))
-- Remove leading and trailing Spaces
Update
[Basname]. [DBO]. [IP]
Set [country] = rtrim (ltrim ([country])

, [Local] = rtrim (ltrim ([local])
-- Convert the IP address to decimal and write it to the new table
Insert
[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 test:

-- Test
Declare @ ipnumber numeric
Set @ ipnumber =
DBO. x16tode ('2017. 140.31.91 ')
Select [startip]
, [Endip]

, [Country]
, [Local]
From [basname]. [DBO]. [ip_real]
Where
[Startip] <= @ ipnumber and [endip]> = @ ipnumber

Note: The x16tode conversion method can be used, but the cutting method is not recommended. Many data in the pure version is irregular.