Sql Server 中漢字處理排序規則，全角半角

--1.?為數據庫指定排序規則
CREATE?DATABASE?db?COLLATE?Chinese_PRC_CI_AS
GO

ALTER?DATABASE?db?COLLATE?Chinese_PRC_BIN
GO


/**//*====================================*/


--2.?為表中的列指定排序規則
CREATE?TABLE?tb(
col1?varchar(10),
col2?varchar(10)?COLLATE?Chinese_PRC_CI_AS)
GO

ALTER?TABLE?tb?ADD?col3?varchar(10)?COLLATE?Chinese_PRC_BIN
GO

ALTER?TABLE?tb?ALTER?COLUMN?col2?varchar(10)?COLLATE?Chinese_PRC_BIN
GO


/**//*====================================*/


--3.?為字符變量和參數應用排序規則
DECLARE?@a?varchar(10),@b?varchar(10)
SELECT?@a='a',@b='A'

--使用排序規則?Chinese_PRC_CI_AS
SELECT?CASE?WHEN?@a?COLLATE?Chinese_PRC_CI_AS?=?@b?THEN?'@a=@b'?ELSE?'@a<>@b'?END
--結果:@a=@b

--使用排序規則?Chinese_PRC_BIN
SELECT?CASE?WHEN?@a?COLLATE?Chinese_PRC_BIN?=?@b?THEN?'@a=@b'?ELSE?'@a<>@b'?END
--結果:@a<>@b

--1.?按拼音排序
DECLARE?@t?TABLE(col?varchar(2))
INSERT?@t?SELECT?'中'
UNION?ALL?SELECT?'國'
UNION?ALL?SELECT?'人'

SELECT?*?FROM?@t?ORDER?BY?col?COLLATE?Chinese_PRC_CS_AS_KS_WS
/**//*--結果
col??
----?
國
人
中
--*/
GO


/**//*==========================================*/


--2.?漢字首字母查詢處理用戶定義函數
CREATE?FUNCTION?f_GetPY(@str?nvarchar(4000))
RETURNS?nvarchar(4000)
AS
BEGIN
????DECLARE?@py?TABLE(
????????ch?char(1),
????????hz1?nchar(1)?COLLATE?Chinese_PRC_CS_AS_KS_WS,
????????hz2?nchar(1)?COLLATE?Chinese_PRC_CS_AS_KS_WS)
????INSERT?@py?SELECT?'A',N'吖',N'鏊'
????UNION??ALL?SELECT?'B',N'八',N'簿'
????UNION??ALL?SELECT?'C',N'嚓',N'錯'
????UNION??ALL?SELECT?'D',N'噠',N'跺'
????UNION??ALL?SELECT?'E',N'屙',N'貳'
????UNION??ALL?SELECT?'F',N'發',N'馥'
????UNION??ALL?SELECT?'G',N'旮',N'過'
????UNION??ALL?SELECT?'H',N'鉿',N'蠖'
????UNION??ALL?SELECT?'J',N'丌',N'竣'
????UNION??ALL?SELECT?'K',N'咔',N'廓'
????UNION??ALL?SELECT?'L',N'垃',N'雒'
????UNION??ALL?SELECT?'M',N'媽',N'穆'
????UNION??ALL?SELECT?'N',N'拿',N'糯'
????UNION??ALL?SELECT?'O',N'噢',N'漚'
????UNION??ALL?SELECT?'P',N'趴',N'曝'
????UNION??ALL?SELECT?'Q',N'七',N'群'
????UNION??ALL?SELECT?'R',N'蚺',N'箬'
????UNION??ALL?SELECT?'S',N'仨',N'鎖'
????UNION??ALL?SELECT?'T',N'他',N'籜'
????UNION??ALL?SELECT?'W',N'哇',N'鋈'
????UNION??ALL?SELECT?'X',N'夕',N'蕈'
????UNION??ALL?SELECT?'Y',N'丫',N'蘊'
????UNION??ALL?SELECT?'Z',N'匝',N'做'
????DECLARE?@i?int
????SET?@i=PATINDEX('%[吖-做]%'?COLLATE?Chinese_PRC_CS_AS_KS_WS,@str)
????WHILE?@i>0
????????SELECT?@str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
????????????,@i=PATINDEX('%[吖-做]%'?COLLATE?Chinese_PRC_CS_AS_KS_WS,@str)
????????FROM?@py
????????WHERE?SUBSTRING(@str,@i,1)?BETWEEN?hz1?AND?hz2
????RETURN(@str)
END
GO

--1．查詢區分全角與半角字符
--測試數據
DECLARE?@t?TABLE(col?varchar(10))
INSERT?@t?SELECT?'aa'
UNION?ALL?SELECT?'Aa'
UNION?ALL?SELECT?'ＡＡ'??????--全角Ａ
UNION?ALL?SELECT?'Ａ,Ａ'????????--全角Ａ,半角逗號(,)
UNION?ALL?SELECT?'Ａ，Ａ'????--全角Ａ,全角逗號(，)

--1.查大寫字母
SELECT?*?FROM?@t
WHERE?col?COLLATE?Chinese_PRC_CS_AS_WS?like?'%A%'

--2.查全角字母
SELECT?*?FROM?@t
WHERE?col?COLLATE?Chinese_PRC_CS_AS_WS?like?'%Ａ%'

--3.查半角逗號(,)
SELECT?*?FROM?@t
WHERE?col?COLLATE?Chinese_PRC_CS_AS_WS?like?'%,%'

--3.查全角逗號(，)
SELECT?*?FROM?@t
WHERE?col?COLLATE?Chinese_PRC_CS_AS_WS?like?'%，%'
GO


/**//*=============================================*/


--2?實現全角與半角字符轉換的處理函數
CREATE?FUNCTION?f_Convert(
@str?NVARCHAR(4000),?--要轉換的字符串
@flag?bit??????????????--轉換標志,0轉換成半角,1轉換成全角
)RETURNS?nvarchar(4000)
AS
BEGIN
????DECLARE?@pat?nvarchar(8),@step?int,@i?int,@spc?int
????IF?@flag=0
????????SELECT?@pat=N'%[！-～]%',@step=-65248,
????????????@str=REPLACE(@str,N'　',N'?')
????ELSE
????????SELECT?@pat=N'%[!-~]%',@step=65248,
????????????@str=REPLACE(@str,N'?',N'　')
????SET?@i=PATINDEX(@pat?COLLATE?LATIN1_GENERAL_BIN,@str)
????WHILE?@i>0
????????SELECT?@str=REPLACE(@str,
????????????????SUBSTRING(@str,@i,1),
????????????????NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
????????????,@i=PATINDEX(@pat?COLLATE?LATIN1_GENERAL_BIN,@str)
????RETURN(@str)
END
GO