binary(1)) +
CAST(CAST(PARSENAME(strIP,3) AS int) AS binary(1)) +
CAST(CAST(PARSENAME(strIP,2) AS int) AS binary(1)) +
CAST(CAST(PARSENAME(strIP,1) AS int) AS binary(1)),
intIP_new = CAST(PARSENAME(strIP,1) AS bigint) +
CAST(PARSENAME(strIP,2) AS bigint) * 256 +
CAST(PARSENAME(strIP,3) AS bigint) * 65536 +
CAST(PARSENAME(strIP,4) AS bigint) * 16777216 --int类型也可以，但浪费空间且不直观
FROM #IPs
6. 中文字符处理
有用指数：★★★★☆
SQLServer中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。
--ASCII字符
SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 32 AND 126
--UNICODE中文字符
SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 19968 AND 40869
19968 0x4E00 一
40869 0x9FA5
--以下两个条件用来判断字符串是否包含汉字
LIKE N'%[吖- ]%' COLLATE Chinese_PRC_CI_AS
LIKE N'%[一- ]%' COLLATE Chinese_PRC_BIN
--这是因为在以上两种不同的排序规则下，汉字的排列顺序是不同的。
--中文全角标点符号
SELECT n,x=CAST(n AS binary(2)),uq=NCHAR(n),ub=NCHAR(n-65248) FROMas 规格3
全角半角标点的转换：
--full2half
CREATE FUNCTION [dbo].[full2half](
@String nvarchar(max)
)
RETURNS nvarchar(max)
AS
/*
全角(Fullwidth)转换为半角(Halfwidth)
*/
BEGIN
DECLARE @chr nchar(1)
DECLARE @i int
SET @String = REPLACE(@String,N' ',N' ')
SET @i = PATINDEX(N'%[！-～]%' COLLATE Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET @chr = SUBSTRING(@String,@i,1)
SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)-65248))
SET @i = PATINDEX(N'%[！-～]%' COLLATE Latin1_General_BIN,@String)
END
RETURN @String
END
GO
CREATE FUNCTION [dbo].[half2full](
@String nvarchar(max)
)
RETURNS nvarchar(max)
AS
/*
半角(Halfwidth)转换为全角(Fullwidth)
*/
BEGIN
DECLARE @chr nchar(1)
DECLARE @i int
SET @String = REPLACE(@String,N' ',N' ')
SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET @chr = SUBSTRING(@String,@i,1)
SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)+65248))
SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String)
END
RETURN @String
END
GO
7. binary字符串
有用指数：★☆☆☆☆
0x1234与'0x1234'的相互转换。很明显，CAST/CONVERT是不行的。
--string到binary可以用这个系统函数sys.fn_varbintohexstr()（实际上是master.dbo.fn_varbintohexstr）
SELECT sys.fn_varbintohexstr(0x1234),'0x1234'
--binary到string需要自定义函数
CREATE FUNCTION dbo.hexstr2varbin(
@hexstr varchar(max)
)
RETURNS varbinary(max)
AS
/*
将表示16进制的字符串转换为2进制类型
--TESTCASES
SELECT dbo.hexstr2varbin(NULL),NULL
SELECT dbo.hexstr2varbin(''),0x
SELECT dbo.hexstr2varbin('0x'),0x
SELECT dbo.hexstr2varbin('30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x30394161'),0x30394161
SELECT dbo.hexstr2varbin('0x1A2B3C4D5E6F'),0x1A2B3C4D5E6F
SELECT dbo.hexstr2varbin('0x1a2b3c4d5e6f'),0x1a2b3c4d5e6f
--UNIMPLEMENTED
SELECT dbo.hexstr2varbin('0x3039416'),0x3039416
*/
BEGIN
DECLARE @value int
DECLARE @ascii int
DECLARE @varbin varbinary(max)
IF @hexstr LIKE '0x%'
SET @hexstr = STUFF(@hexstr,1,2,'')
SET @hexstr = UPPER(@hexstr)
IF @hexstr NOT LIKE '%[^0-9A-F]%' COLLATE Chinese_PRC_BIN
BEGIN
SET @varbin = 0x
WHILE @hexstr <> ''
BEGIN
SET @value = ASCII(SUBSTRING(@hexstr,1,1))
IF @value <= 57
SET @value = @value - 48
ELSE
SET @value = @value - 55
SET @ascii = @value * 16