--创建表存储IP地址 -- Given the following IPs table CREATE TABLE dbo.IPs(ip varchar(15) NOT NULL PRIMARY KEY); --创建视图 -- Add a CHECK constraint that validates IPs ALTER TABLE dbo.IPs ADD CONSTRAINT CHK_IP_valid CHECK ( -- 3 periods and no empty octets ip LIKE '_%._%._%._%' AND -- not 4 periods or more ip NOT LIKE '%.%.%.%.%' AND -- no characters other than digits and periods ip NOT LIKE '%[^0-9.]%' AND -- not more than 3 digits per octet ip NOT LIKE '%[0-9][0-9][0-9][0-9]%' AND -- NOT 300 - 999 ip NOT LIKE '%[3-9][0-9][0-9]%' AND -- NOT 260 - 299 ip NOT LIKE '%2[6-9][0-9]%' AND -- NOT 256 - 259 ip NOT LIKE '%25[6-9]%' );-- Test valid INSERT INTO dbo.IPs VALUES('131.107.2.201'); INSERT INTO dbo.IPs VALUES('131.33.2.201'); INSERT INTO dbo.IPs VALUES('131.33.2.202'); INSERT INTO dbo.IPs VALUES('3.107.2.4'); INSERT INTO dbo.IPs VALUES('3.107.3.169'); INSERT INTO dbo.IPs VALUES('3.107.104.172'); INSERT INTO dbo.IPs VALUES('22.107.202.123'); INSERT INTO dbo.IPs VALUES('22.20.2.77'); INSERT INTO dbo.IPs VALUES('22.156.9.91'); INSERT INTO dbo.IPs VALUES('22.156.89.32');-- Test invalid INSERT INTO dbo.IPs VALUES('1.1.1.256'); INSERT INTO dbo.IPs VALUES('1.1.1.1.1'); INSERT INTO dbo.IPs VALUES('1.1.1'); INSERT INTO dbo.IPs VALUES('1..1.1'); INSERT INTO dbo.IPs VALUES('.1.1.1'); INSERT INTO dbo.IPs VALUES('a.1.1.1'); GO--枚举N种方法 -- Parsing IPs CREATE VIEW dbo.IPPatterns ASSELECT REPLICATE('_', N1.n) + '.' + REPLICATE('_', N2.n) + '.' + REPLICATE('_', N3.n) + '.' + REPLICATE('_', N4.n) AS pattern, N1.n AS l1, N2.n AS l2, N3.n AS l3, N4.n AS l4, 1 AS s1, N1.n+2 AS s2, N1.n+N2.n+3 AS s3, N1.n+N2.n+N3.n+4 AS s4 FROM dbo.Nums AS N1, dbo.Nums AS N2, dbo.Nums AS N3, dbo.Nums AS N4 WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3; GO-- Parsed IPs SELECT ip, CAST(SUBSTRING(ip, s1, l1) AS TINYINT) AS o1, CAST(SUBSTRING(ip, s2, l2) AS TINYINT) AS o2, CAST(SUBSTRING(ip, s3, l3) AS TINYINT) AS o3, CAST(SUBSTRING(ip, s4, l4) AS TINYINT) AS o4 FROM dbo.IPs JOIN dbo.IPPatterns ON ip LIKE pattern ORDER BY o1, o2, o3, o4; GO
--创建表存储IP地址
-- Given the following IPs table
CREATE TABLE dbo.IPs(ip varchar(15) NOT NULL PRIMARY KEY);
--创建视图
-- Add a CHECK constraint that validates IPs
ALTER TABLE dbo.IPs ADD CONSTRAINT CHK_IP_valid CHECK
(
-- 3 periods and no empty octets
ip LIKE '_%._%._%._%'
AND
-- not 4 periods or more
ip NOT LIKE '%.%.%.%.%'
AND
-- no characters other than digits and periods
ip NOT LIKE '%[^0-9.]%'
AND
-- not more than 3 digits per octet
ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
AND
-- NOT 300 - 999
ip NOT LIKE '%[3-9][0-9][0-9]%'
AND
-- NOT 260 - 299
ip NOT LIKE '%2[6-9][0-9]%'
AND
-- NOT 256 - 259
ip NOT LIKE '%25[6-9]%'
);-- Test valid
INSERT INTO dbo.IPs VALUES('131.107.2.201');
INSERT INTO dbo.IPs VALUES('131.33.2.201');
INSERT INTO dbo.IPs VALUES('131.33.2.202');
INSERT INTO dbo.IPs VALUES('3.107.2.4');
INSERT INTO dbo.IPs VALUES('3.107.3.169');
INSERT INTO dbo.IPs VALUES('3.107.104.172');
INSERT INTO dbo.IPs VALUES('22.107.202.123');
INSERT INTO dbo.IPs VALUES('22.20.2.77');
INSERT INTO dbo.IPs VALUES('22.156.9.91');
INSERT INTO dbo.IPs VALUES('22.156.89.32');-- Test invalid
INSERT INTO dbo.IPs VALUES('1.1.1.256');
INSERT INTO dbo.IPs VALUES('1.1.1.1.1');
INSERT INTO dbo.IPs VALUES('1.1.1');
INSERT INTO dbo.IPs VALUES('1..1.1');
INSERT INTO dbo.IPs VALUES('.1.1.1');
INSERT INTO dbo.IPs VALUES('a.1.1.1');
GO--枚举N种方法
-- Parsing IPs
CREATE VIEW dbo.IPPatterns
ASSELECT
REPLICATE('_', N1.n) + '.' + REPLICATE('_', N2.n) + '.'
+ REPLICATE('_', N3.n) + '.' + REPLICATE('_', N4.n) AS pattern,
N1.n AS l1, N2.n AS l2, N3.n AS l3, N4.n AS l4,
1 AS s1, N1.n+2 AS s2, N1.n+N2.n+3 AS s3, N1.n+N2.n+N3.n+4 AS s4
FROM dbo.Nums AS N1, dbo.Nums AS N2, dbo.Nums AS N3, dbo.Nums AS N4
WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3;
GO-- Parsed IPs
SELECT ip,
CAST(SUBSTRING(ip, s1, l1) AS TINYINT) AS o1,
CAST(SUBSTRING(ip, s2, l2) AS TINYINT) AS o2,
CAST(SUBSTRING(ip, s3, l3) AS TINYINT) AS o3,
CAST(SUBSTRING(ip, s4, l4) AS TINYINT) AS o4
FROM dbo.IPs
JOIN dbo.IPPatterns
ON ip LIKE pattern
ORDER BY o1, o2, o3, o4;
GO
什么样的..
cast (字段 as varchar(20))
CString AddrToString(DWORD dwAddr)
{
CString szAddr;
BYTE *addr = (BYTE*)&dwAddr;
//addr[0] = (BYTE)dwAddr&0xFF;
//addr[1] = (BYTE)(dwAddr>>8)&0xFF;
//addr[2] = (BYTE)(dwAddr>>16)&0xFF;
//addr[3] = (BYTE)(dwAddr>>24)&0xFF;
szAddr.Format("%d.%d.%d.%d", addr[3], addr[2], addr[1], addr[0]);
return szAddr;
}我的要求是将这个功能使用SQL实现
addr.sin_addr.s_addr = 整型数;
printf("%s",inet_ntoa(addr.sin_addr))inet_ntoa返回的就是字符串