汉字字段按拼音首字母实现分组排序

有两种方法：
第用：用.net类实现：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/*
* 说明:此类用于将一个字符串找出首字声母,用法:ChineseConvert.UtilIndexCode(str);
*
*
*/
/// <summary>
/// ChineseConvert 的摘要说明
/// </summary>
public class ChineseConvert
{
public ChineseConvert()
{
}
/// <summary>
/// 返回字符串的首写字母字符串
/// </summary>
/// <param name="IndexTxt">需得到首写字母的字符串</param>
/// <returns></returns>
public static String UtilIndexCode(String IndexTxt)
{
string _Temp = null;
for (int i = 0; i < IndexTxt.Length; i++)
_Temp = _Temp + GetOneIndex(IndexTxt.Substring(i, 1));
return _Temp;
}

//得到单个字符的首字母
private static String GetOneIndex(String OneIndexTxt)
{
if (Convert.ToChar(OneIndexTxt) >= 0 && Convert.ToChar(OneIndexTxt) < 256)
return OneIndexTxt;
else
return GetGbkX(OneIndexTxt);
}

//根据汉字拼音排序得到首字母
private static string GetGbkX(string str)
{
if (str.CompareTo("吖") < 0)
{
return str;
}
if (str.CompareTo("八") < 0)
{
return "A";
}

if (str.CompareTo("嚓") < 0)
{
return "B";
}

if (str.CompareTo("咑") < 0)
{
return "C";
}
if (str.CompareTo("妸") < 0)
{
return "D";
}
if (str.CompareTo("发") < 0)
{
return "E";
}
if (str.CompareTo("旮") < 0)
{
return "F";
}
if (str.CompareTo("铪") < 0)
{
return "G";
}
if (str.CompareTo("讥") < 0)
{
return "H";
}
if (str.CompareTo("咔") < 0)
{
return "J";
}
if (str.CompareTo("垃") < 0)
{
return "K";
}
if (str.CompareTo("呒") < 0)
{
return "L";
}
if (str.CompareTo("拏") < 0)
{
return "M";
}
if (str.CompareTo("噢") < 0)
{
return "N";
}
if (str.CompareTo("妑") < 0)
{
return "O";
}
if (str.CompareTo("七") < 0)
{
return "P";
}
if (str.CompareTo("亽") < 0)
{
return "Q";
}
if (str.CompareTo("仨") < 0)
{
return "R";
}
if (str.CompareTo("他") < 0)
{
return "S";
}
if (str.CompareTo("哇") < 0)
{
return "T";
}
if (str.CompareTo("夕") < 0)
{
return "W";
}
if (str.CompareTo("丫") < 0)
{
return "X";
}
if (str.CompareTo("帀") < 0)
{
return "Y";
}
if (str.CompareTo("咗") < 0)
{
return "Z";
}
return str;
}
}



第二，用数据库函数实现：
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '呒','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go

查询：如 按字母a排序
select * from 表名 where left(dbo.f_GetPy(列名),1) = 'a'

转载于:https://www.cnblogs.com/activities/archive/2011/09/18/2180533.html