/*--获得汉字字符串的首字母--*/ 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
呵呵,我刚做完一个模糊查询,就是用的拼音查的。首先,在数据库中建立一个取首字母的函数(后边我贴的有,网上找的,不知对不对,你可以自己找);然后,在表中添加一列专门放拼音首字母,用上边的函数就是了;最后,查询的时候直接用字母在拼音首字母字段中查就是了。要注意的是,以后添加数据的时候也要记得添加拼音首字母。取得汉子拼音首字母的函数: 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
你应该要获取这个姓名的第一个汉字的首字母拼音。
当然最好的办法是,写一个类,它能够自动获取这个首字母拼音,并加入库中的一个字段select的时候就非常方便了,
/*--获得汉字字符串的首字母--*/
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
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
{
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 "";
}
}