【转载】MSSQL汉字首字母查询处理自定义函数
2024-09-05 08:27:25
-- 汉字首字母查询处理用户定义函数
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
最新文章
- SDK接入(3)之iOS内支付(In-App Purchase)接入
- Asp.Net MVC4入门指南(6):验证编辑方法和编辑视图
- Java对象表示方式2:XStream实现对对象的XML化
- linux centos使用xrdp远程界面登陆
- JDBC事务处理
- 命名空间中的“MvcBuildViews”。 无效
- ovirt user guide
- Mysql5.6.24 zip解压缩版配置及修改默认编码方法
- 7,C++ public, protected, private 继承的区别
- linux常用脚本
- h1b期间回国须知
- java aio nio bio
- Markdown Cheatsheet
- 哈希表(散列表),Hash表漫谈
- [转]asp+oracle分页
- Android 开发进入Linux系统执行命令 2018-5-25 Fri.
- 洛谷P3209平面图判定 [HNOI2010] 2-sat
- 理解OAuth 2.0 (摘自阮一峰网络日志)
- C# XML创建解析、XML格式化
- 4.nginx高可用