--
1. 为数据库指定排序规则
CREATE
DATABASE
db COLLATE Chinese_PRC_CI_AS
GO
![](/Images/OutliningIndicators/None.gif)
ALTER
DATABASE
db COLLATE Chinese_PRC_BIN
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*====================================*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--
2. 为表中的列指定排序规则
CREATE
TABLE
tb(
col1
varchar
(
10
),
col2
varchar
(
10
) COLLATE Chinese_PRC_CI_AS)
GO
![](/Images/OutliningIndicators/None.gif)
ALTER
TABLE
tb
ADD
col3
varchar
(
10
) COLLATE Chinese_PRC_BIN
GO
![](/Images/OutliningIndicators/None.gif)
ALTER
TABLE
tb
ALTER
COLUMN
col2
varchar
(
10
) COLLATE Chinese_PRC_BIN
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*====================================*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--
3. 为字符变量和参数应用排序规则
DECLARE
@a
varchar
(
10
),
@b
varchar
(
10
)
SELECT
@a
=
'
a
'
,
@b
=
'
A
'
![](/Images/OutliningIndicators/None.gif)
--
使用排序规则 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
'
人
'
![](/Images/OutliningIndicators/None.gif)
SELECT
*
FROM
@t
ORDER
BY
col COLLATE Chinese_PRC_CS_AS_KS_WS
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*--结果
col
----
国
人
中
--*/
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*==========================================*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--
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
'
AA
'
--
全角A
UNION
ALL
SELECT
'
A,A
'
--
全角A,半角逗号(,)
UNION
ALL
SELECT
'
A,A
'
--
全角A,全角逗号(,)
--
1.查大写字母
SELECT
*
FROM
@t
WHERE
col COLLATE Chinese_PRC_CS_AS_WS
like
'
%A%
'
![](/Images/OutliningIndicators/None.gif)
--
2.查全角字母
SELECT
*
FROM
@t
WHERE
col COLLATE Chinese_PRC_CS_AS_WS
like
'
%A%
'
![](/Images/OutliningIndicators/None.gif)
--
3.查半角逗号(,)
SELECT
*
FROM
@t
WHERE
col COLLATE Chinese_PRC_CS_AS_WS
like
'
%,%
'
![](/Images/OutliningIndicators/None.gif)
--
3.查全角逗号(,)
SELECT
*
FROM
@t
WHERE
col COLLATE Chinese_PRC_CS_AS_WS
like
'
%,%
'
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*=============================================*/
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--
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
转载于:https://www.cnblogs.com/nosnowwolf/archive/2008/04/02/1134550.html