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 表
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 表
/// 汉字转拼音缩写
/// Code By [email protected]
/// 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 [email protected]
/// 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 "*";
}
http://community.csdn.net/Expert/topic/3813/3813389.xml?temp=.9660761
不过现在把选择的语句插入一张表里是不是这样做的
好象有错误!
select ID ,nameid from 表1
insert into 表2(ID ,nameID)
我以前用过,没问题!
但现在怎么也搞不清为什么错了
select ID ,nameid from 表1
但是还有一个问题!
我转化后的名字中间后空格!我用软件转化的。
象这样xue li dong 我怎么变成xuelidong 啊
我用TRIM怎么不认识!
用replace应该可以!
但是TRIM 怎么不行!
还请指教!
回答了就揭贴了
TO:vivianfdlpw()
回答了分都是你的了