＋设为首页 ＋添加到收藏夹
我现在有1000多个姓名，每一个都是中文的，现在我想把这些中文姓名都转化成汉语拼音的形式。
不知道大家有没有高见！
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
--调用
select dbo.f_GetPy(Name) from 表
/// <summary>
/// 汉字转拼音缩写
/// Code By MuseStudio@hotmail.com
/// 2004-11-30
/// </summary>
/// <param name="str">要转换的汉字字符串</param>
/// <returns>拼音缩写</returns>
public string GetPYString(string str)
{
string tempStr = "";
foreach(char c in str)
{
if((int)c >= 33 && (int)c <=126)
{//字母和符号原样保留
tempStr += c.ToString();
}
else
{//累加拼音声母
tempStr += GetPYChar(c.ToString());
}
}
return tempStr;
}
/// <summary>
/// 取单个字符的拼音声母
/// Code By MuseStudio@hotmail.com
/// 2004-11-30
/// </summary>
/// <param name="c">要转换的单个汉字</param>
/// <returns>拼音声母</returns>
public string GetPYChar(string c)
{
byte[] array = new byte[2];
array = System.Text.Encoding.Default.GetBytes(c);
int i = (short)(array[0] - \0) * 256 + ((short)(array[1] - \0));
if ( i < 0xB0A1) return "*";
if ( i < 0xB0C5) return "a";
if ( i < 0xB2C1) return "b";
if ( i < 0xB4EE) return "c";
if ( i < 0xB6EA) return "d";
if ( i < 0xB7A2) return "e";
if ( i < 0xB8C1) return "f";
if ( i < 0xB9FE) return "g";
if ( i < 0xBBF7) return "h";
if ( i < 0xBFA6) return "g";
if ( i < 0xC0AC) return "k";
if ( i < 0xC2E8) return "l";
if ( i < 0xC4C3) return "m";
if ( i < 0xC5B6) return "n";
if ( i < 0xC5BE) return "o";
if ( i < 0xC6DA) return "p";
if ( i < 0xC8BB) return "q";
if ( i < 0xC8F6) return "r";
if ( i < 0xCBFA) return "s";
if ( i < 0xCDDA) return "t";
if ( i < 0xCEF4) return "w";
if ( i < 0xD1B9) return "x";
if ( i < 0xD4D1) return "y";
if ( i < 0xD7FA) return "z";
return "*";
}
--sql里没有trim,只有ltrim,rtrim,不过也只是截前后的空格,要去掉所有空格,可以这样写
select replace(列, ,) from 表
