SQL中英文混合排序
来源:互联网 发布:软件评测中心 青云 编辑:程序博客网 时间:2024/05/13 02:43
首先要创建一个存储过程
create function [dbo].[fn_ChineseToSpell](@strChinese varchar(500)='')
returns varchar(500)
as
begin /*函数实现开始*/
declare @strLen int,@return varchar(500),@i int
declare @n int,@c char(1),@chn nchar(1)
select @strLen=len(@strChinese),@return='',@i=0
while @i<@strLen
begin /*while循环开始*/
select @i=@i+1,@n=63,@chn=substring(@strChinese,@i,1)
if @chn>'z'/*原理:“字符串排序以及ASCII码表”*/
select @n = @n +1,@c =case chn when @chn then char(@n) else @c end from(select top 27 * from (select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' /*because have no 'i'*/ union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' /*no 'u'*/ union all select '屲' /*no 'v'*/ union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b
else
set @c=@chn
set @return=@return+@c
end /*while循环结束*/
return(@return)
end /*函数实现结束*/
returns varchar(500)
as
begin /*函数实现开始*/
declare @strLen int,@return varchar(500),@i int
declare @n int,@c char(1),@chn nchar(1)
select @strLen=len(@strChinese),@return='',@i=0
while @i<@strLen
begin /*while循环开始*/
select @i=@i+1,@n=63,@chn=substring(@strChinese,@i,1)
if @chn>'z'/*原理:“字符串排序以及ASCII码表”*/
select @n = @n +1,@c =case chn when @chn then char(@n) else @c end from(select top 27 * from (select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' /*because have no 'i'*/ union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' /*no 'u'*/ union all select '屲' /*no 'v'*/ union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b
else
set @c=@chn
set @return=@return+@c
end /*while循环结束*/
return(@return)
end /*函数实现结束*/
然后就可以用了。substring 转换为英文首先字母
select substring(dbo.[fn_ChineseToSpell](name),1,1) as fw,* from member order by fw
0 0
- SQL中英文混合排序
- 数据库中英文混合排序
- Mysql 中英文混合排序
- iOS 中英文混合排序
- 中英文混合排序
- 中英文混合排序
- [简易]中英文混合排序
- 中英文混合排序
- 中英文及数字混合排序
- android联系人中英文混合排序
- android下中英文混合排序
- 中英文混合按照首字母排序
- Android按名称排序,中英文混合排序
- 中文、英文和中英文混合排序
- sql 中英文 混合 字符串截取函数
- MySQL中英文混合排序最简单的办法
- 38、微信通讯录联系人汉语拼音中英文混合排序
- 中英文混合分词
- Arduino: Ultrasonic Sensor & Motor Car
- 关于字符串读入的总结
- iOS之推送通知-本地-服务器3.服务器端实现: 如果要编写内容提供者的推送服务程序,需要进行SSL认证编程,以及构建APNS数据包,数据包分为3个主要部分:Command(命令)、deviceTo
- dumpbin用法
- 进程同步和线程同步
- SQL中英文混合排序
- vmware克隆linux出现Device eth0 does not seem to be present
- python使用re, os, httplib, urllib批量下载网页上的图片
- 父亲给我的一份信
- Thinking In Java笔记(第七章 复用类)
- HBASE Shell
- 设置IE进程数
- android应用程序如何调用支付宝接口
- ZOJ 3645 (简单的高斯消元)