|
一、排序规则应用示例
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,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 '%,%'
（4--查全角逗号(，)
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,N' ',N' ') ?SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) ?WHILE @i>0 ??SELECT @str=REPLACE(@str,????SUBSTRING(@str,????NCHAR(UNICODE(SUBSTRING(@str,1))+@step)) ???,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) ?RETURN(@str) END GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
