按照拼音首字母查人名怎么查？

gragon 2006-07-12 05:00:12

例如。条件给了C。要查所有人名，什么陈××，程××，迟××，曹××的都要查到！这个该怎么做呢

...全文

1703 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

有你_才有梦想 2007-02-04

打赏
举报

xyunsh 2006-07-12

打赏
举报

public sealed class FirstLetter
{
private static readonly string _codeData = "CJWGNSPGCENEGYPBTWXZDXYKYGTPJNMJQMBSGZSCYJSYYFPGGBZGYDYWJKGALJSWKBJQHYJWPDZLSGMR"
+ "YBYWWCCGZNKYDGTTNGJEYEKZYDCJNMCYLQLYPYQBQRPZSLWBDGKJFYXJWCLTBNCXJJJJCXDTQSQZYCDXXHGCKBPHFFSS"
+ "PYBGMXJBBYGLBHLSSMZMPJHSOJNGHDZCDKLGJHSGQZHXQGKEZZWYMCSCJNYETXADZPMDSSMZJJQJYZCJJFWQJBDZBJGD"
+ "NZCBWHGXHQKMWFBPBQDTJJZKQHYLCGXFPTYJYYZPSJLFCHMQSHGMMXSXJPKDCMBBQBEFSJWHWWGCKPYLQBGLDLCCTNMA"
+ "EDDKSJNGKCSGXLHZAYBDBTSDKDYLHGYMYLCXPYCJNDQJWXQXFYYFJLEJBZRWCCQHQCSBZKYMGPLBMCRQCFLNYMYQMSQT"
+ "RBCJTHZTQFRXCHXMCJCJLXQGJMSHZKBSWXEMDLCKFSYDSGLYCJJSSJNQBJCTYHBFTDCYJDGWYGHQFRXWCKQKXEBPDJPX"
+ "JQSRMEBWGJLBJSLYYSMDXLCLQKXLHTJRJJMBJHXHWYWCBHTRXXGLHJHFBMGYKLDYXZPPLGGPMTCBBAJJZYLJTYANJGBJ"
+ "FLQGDZYQCAXBKCLECJSZNSLYZHLXLZCGHBXZHZNYTDSBCJKDLZAYFFYDLABBGQSZKGGLDNDNYSKJSHDLXXBCGHXYGGDJ"
+ "MMZNGMMCCGWZSZXSJBZNMLZDTHCQYDBDLLSCDDNLKJYHJSYCJLKOHQASDHNHCSGAEHDAASHTCPLCPQYBSDMPJLPCJAQL"
+ "CDHJJASPRCHNGJNLHLYYQYHWZPNCCGWWMZFFJQQQQXXACLBHKDJXDGMMYDJXZLLSYGXGKJRYWZWYCLZMCSJZLDBNDCFC"
+ "XYHLSCHYCJQPPQAGMNYXPFRKSSBJLYXYJJGLNSCMHCWWMNZJJLHMHCHSYPPTTXRYCSXBYHCSMXJSXNBWGPXXTAYBGAJC"
+ "XLYPDCCWQOCWKCCSBNHCPDYZNBCYYTYCKSKYBSQKKYTQQXFCWCHCWKELCQBSQYJQCCLMTHSYWHMKTLKJLYCHWHEQJHTJ"
+ "HPPQPQSCFYMMCMGBMHGLGSLLYSDLLLJPCHMJHWLJCYHZJXHDXJLHXRSWLWZJCBXMHZQXSDZPMGFCSGLSDYMJSHXPJXOM"
+ "YQKNMYBLRTHBCFTPMGYXLCHLHLZYLXGSSSSCCLSLDCLEPBHSHXYYFHBMGDFYCNJQWLQHJJCYWJZTEJJDHFBLQXTQKWHD"
+ "CHQXAGTLXLJXMSLJHDZKZJECXJCJNMBBJCSFYWKBJZGHYSDCPQYRSLJPCLPWXSDWEJBJCBCNAYTMGMBAPCLYQBCLZXCB"
+ "NMSGGFNZJJBZSFQYNDXHPCQKZCZWALSBCCJXPOZGWKYBSGXFCFCDKHJBSTLQFSGDSLQWZKXTMHSBGZHJCRGLYJBPMLJS"
+ "XLCJQQHZMJCZYDJWBMJKLDDPMJEGXYHYLXHLQYQHKYCWCJMYHXNATJHYCCXZPCQLBZWWWTWBQCMLBMYNJCCCXBBSNZZL"
+ "JPLJXYZTZLGCLDCKLYRZZGQTGJHHGJLJAXFGFJZSLCFDQZLCLGJDJCSNCLLJPJQDCCLCJXMYZFTSXGCGSBRZXJQQCCZH"
+ "GYJDJQQLZXJYLDLBCYAMCSTYLBDJBYREGKLZDZHLDSZCHZNWCZCLLWJQJJJKDGJCOLBBZPPGLGHTGZCYGEZMYCNQCYCY"
+ "HBHGXKAMTXYXNBSKYZZGJZLQJDFCJXDYGJQJJPMGWGJJJPKJSBGBMMCJSSCLPQPDXCDYYKYPCJDDYYGYWCHJRTGCNYQL"
+ "DKLJCZZGZCCJGDYKSGPZMDLCPHNJAFYZDJCNMWESCSGLBTZCGMSDLLYXQSXSBLJSBBSGGHFJLWPMZJNLYYWDQSHZXTYY"
+ "WHMCYHYWDBXBTLMSWYYFSBJCBDXXLHJHFPSXZQHFZMQCZTQCXZXRDKDJHNNYZQQFNQDMMGNYDXMJGDHCDYCBFFALLZTD"
+ "LTFKMXQZDNGEQDBDCZJDXBZGSQQDDJCMBKXFFXMKDMCSYCHZCMLJDJYNHPRSJMKMPCKLGDBQTFZSWTFGGLYPLLJZHGJJ"
+ "GYPZLTCSMCNBTJBHFKDHBYZGKPBBYMTDLSXSBNPDKLEYCJNYCDYKZDDHQGSDZSCTARLLTKZLGECLLKJLJJAQNBDGGGHF"
+ "JTZQJSECSHALQFMMGJNLYJBBTMLYCXDCJPLDLPCQDHSYCBZSCKBZMSLJFLHRBJSNBRGJHXPDGDJYBZGDLGCSEZGXLBLG"
+ "YXTWMABCHECMWYJYZLLJJSHLGNDJLSLYGKDZPZXJYYZLPCXSZFGWYYDLYHCLJSCMBJHBLYJLYCBLYDPDQYSXKTBYTDKD"
+ "XJYPCNRJMFDJGKLCCJBCTBJDDBBLBLCDQRPPXJCGLZCSHLTOLJNMDDDLNGKAQAKGJGYHHEZNMSHRPHQQJCHGMFPRXCJG"
+ "DYCHGHLYRZQLCNGJNZSQDKQJYMSZSWLCFQJQXGBGGXMDJWLMCRNFKKFSYYLJBMQAMMMYCCTBSHCPTXXZZSMPHFSHMCLM"
+ "LDJFYQXSDYJDJJZZHQPDSZGLSSJBCKBXYQZJSGPSXJZQZNQTBDKWXJKHHGFLBCSMDLDGDZDBLZKYCQNNCSYBZBFGLZZX"
+ "SWMSCCMQNJQSBDQSJTXXMBLDXCCLZSHZCXRQJGJYLXZFJPHYMZQQYDFQJJLCZNZJCDGZYGCDXMZYSCTLKPHTXHTLBJXJ"
+ "LXSCDQCCBBQJFQZFSLTJBTKQBSXJJLJCHCZDBZJDCZJCCPRNLQCGPFCZLCLCXZDMXMPHGSGZGSZZQJXLWTJPFSYASLCJ"
+ "BTCKWCWMYTCSJJLJCQLWZMALBXYFBPNLSCHTGJWEJJXXGLLJSTGSHJQLZFKCGNNDSZFDEQFHBSAQDGYLBXMMYGSZLDYD"
+ "JMJJRGBJGKGDHGKBLGKBDMBYLXWCXYTTYBKMRJJZXQJBHLMHMJJZMQASLDCYXYQDLQCAFYWYXQHZ";

public static string GetFirstLetter(string input)
{
if (string.IsNullOrEmpty(input))
return input;

string first = input.Substring(0, 1);
if (Convert.ToChar(first) >= 0 && Convert.ToChar(first) < 256)
return first.ToUpper();
else
{
Encoding gb2312 = Encoding.GetEncoding("gb2312");
byte[] unicodeBytes = Encoding.Unicode.GetBytes(first);
byte[] gb2312Bytes = Encoding.Convert(Encoding.Unicode, gb2312, unicodeBytes);

return GetLetter(Convert.ToInt32(
String.Format("{0:D2}", Convert.ToInt16(gb2312Bytes[0]) - 160)
+ String.Format("{0:D2}", Convert.ToInt16(gb2312Bytes[1]) - 160)
));

}
}

private static string GetLetter(int gbCode)
{
if (gbCode >= 1601 && gbCode < 1637) return "A";
if (gbCode >= 1637 && gbCode < 1833) return "B";
if (gbCode >= 1833 && gbCode < 2078) return "C";
if (gbCode >= 2078 && gbCode < 2274) return "D";
if (gbCode >= 2274 && gbCode < 2302) return "E";
if (gbCode >= 2302 && gbCode < 2433) return "F";
if (gbCode >= 2433 && gbCode < 2594) return "G";
if (gbCode >= 2594 && gbCode < 2787) return "H";
if (gbCode >= 2787 && gbCode < 3106) return "J";
if (gbCode >= 3106 && gbCode < 3212) return "K";
if (gbCode >= 3212 && gbCode < 3472) return "L";
if (gbCode >= 3472 && gbCode < 3635) return "M";
if (gbCode >= 3635 && gbCode < 3722) return "N";
if (gbCode >= 3722 && gbCode < 3730) return "O";
if (gbCode >= 3730 && gbCode < 3858) return "P";
if (gbCode >= 3858 && gbCode < 4027) return "Q";
if (gbCode >= 4027 && gbCode < 4086) return "R";
if (gbCode >= 4086 && gbCode < 4390) return "S";
if (gbCode >= 4390 && gbCode < 4558) return "T";
if (gbCode >= 4558 && gbCode < 4684) return "W";
if (gbCode >= 4684 && gbCode < 4925) return "X";
if (gbCode >= 4925 && gbCode < 5249) return "Y";
if (gbCode >= 5249 && gbCode <= 5589) return "Z";
if (gbCode >= 5601 && gbCode <= 8794)
{
string strGBCode = gbCode.ToString();

int pos = (Convert.ToInt16(strGBCode.Substring(0, 2)) - 56) * 94 + Convert.ToInt16(strGBCode.Substring(strGBCode.Length - 2, 2));
return _codeData.Substring(pos - 1, 1);
}
return "";
}
}

afeicn 2006-07-12

打赏
举报

呵呵，我刚做完一个模糊查询，就是用的拼音查的。

首先，在数据库中建立一个取首字母的函数（后边我贴的有，网上找的，不知对不对，你可以自己找）；

然后，在表中添加一列专门放拼音首字母，用上边的函数就是了；

最后，查询的时候直接用字母在拼音首字母字段中查就是了。

要注意的是，以后添加数据的时候也要记得添加拼音首字母。

取得汉子拼音首字母的函数：
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

xyunsh 2006-07-12

打赏
举报

/*--获得汉字字符串的首字母--*/
CREATE function f_GetFirstPy(@str varchar(50)='')
returns char(1)
as
begin
declare @n int, @c char(1), @chn nchar(1)
select @n=63, @chn=substring(@str,1,1)
if @chn > 'z'
if( @chn < '八' ) set @c = 'A'
else if ( @chn < '嚓' ) set @c = 'B'
else if ( @chn < '咑' ) set @c = 'C'
else if ( @chn < '妸' ) set @c = 'D'
else if ( @chn < '发' ) set @c = 'E'
else if ( @chn < '旮' ) set @c = 'F'
else if ( @chn < '铪' ) set @c = 'G'
else if ( @chn < '丌' ) set @c = 'H'
else if ( @chn < '丌' ) set @c = 'I'
else if ( @chn < '咔' ) set @c = 'J'
else if ( @chn < '垃' ) set @c = 'K'
else if ( @chn < '嘸' ) set @c = 'L'
else if ( @chn < '拏' ) set @c = 'M'
else if ( @chn < '噢' ) set @c = 'N'
else if ( @chn < '妑' ) set @c = 'O'
else if ( @chn < '七' ) set @c = 'P'
else if ( @chn < '呥' ) set @c = 'Q'
else if ( @chn < '仨' ) set @c = 'R'
else if ( @chn < '他' ) set @c = 'S'
else if ( @chn < '屲' ) set @c = 'T'
else if ( @chn < '屲' ) set @c = 'U'
else if ( @chn < '屲' ) set @c = 'V'
else if ( @chn < '夕' ) set @c = 'W'
else if ( @chn < '丫' ) set @c = 'X'
else if ( @chn < '帀' ) set @c = 'Y'
else set @c = 'Z'
else if @chn <= 'z' and @chn >= 'a'
set @c = upper(@chn)
else if @chn <= 'Z' and @chn >= 'A'
set @c = @chn
else
set @c = ''
return (@c)
end