如何按照汉字的开头字母的顺序排序

[cnn3]

几条数据,比如陈大明,李立,胜勇,Michanl,Jack
请问怎么按照照开头字母,从Ａ到Ｚ排序

[超级大笨狼]

--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fGetPy]

GO



--创建取拼音函数

create function fGetPy(@Str varchar(500)='')

returns varchar(500)

as

begin

declare @strlen int,@return varchar(500),@ii int

declare @n int,@c char(1),@chn nchar(1)



select @strlen=len(@str),@return='',@ii=0

set @ii=0

while @ii<@strlen

begin

select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)

if @chn>'z'

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='a'

set @return=@return+@c

end

return(@return)

end



go

--测试

select dbo.fgetpy('东莞市') as 东莞市,dbo.fgetpy('ab中c国人') as 中国人



--删除拼音函数

drop function fgetpy

[cnn3]

我记得vb里面有一个函数直接调用就可以实现，在c#里面不知道有没有这种函数

[hedongfu]

--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

[zhou_she]

顶