--SQL Server 2000数据库的解决方案: 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 * from a where dbo.f_GetPy(Name) like 'ZS%'
CREATE TRIGGER aa ON dbo.ccccc FOR INSERT, UPDATE, DELETE AS update ccc set pinyin=dbo.F_GetHelpCode(b.name) from ccc a,inserted b where a.code=b.code CREATE FUNCTION F_GetHelpCode ( @cName VARCHAR(50) ) RETURNS VARCHAR(20) AS BEGIN DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(20), @e VARCHAR(20), @iAscii SMALLINT SELECT @i=1, @L=0 , @cHelpCode='' while @L<=20 AND @i<=LEN(@cName) BEGIN SELECT @e=LOWER(SUBSTRING(@cname,@i,1)) SELECT @iAscii=ASCII(@e) IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95 SELECT @cHelpCode=@cHelpCode +@e ELSE IF @iAscii>=176 AND @iAscii <=247 SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e) ELSE SELECT @L=@L-1 SELECT @i=@i+1, @L=@L+1 END RETURN @cHelpCode END create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63select @n = @n +1, @c = case chn when @chn then char(@n) else @c end from( select top 27 * from ( select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' union all select --because have no 'i' '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' union all select --no 'u' '屲' union all select --no 'v' '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) end
int gb = gbValue(ch); if (gb < table[0]) return '0';
int i; for (i = 0; i < 26; ++i) { if (match(i, gb)) break; }
if (i >= 26) return '0'; else return alphatable[i]; } //根据一个包含汉字的字符串返回一个汉字拼音首字母的字符串 public String getAlpha(String SourceStr,int k) { String Result = ""; int StrLength = SourceStr.length(); int i; int kk=0; if(StrLength >k ) kk = k; else kk = StrLength; try { for (i = 0; i < kk; i++) { Result += Char2Alpha(SourceStr.charAt(i)); } } catch (Exception e) { Result = ""; } return Result; } private boolean match(int i, int gb) { if (gb < table[i]) return false;
int j = i + 1;
//字母Z使用了两个标签 while (j < 26 && (table[j] == table[i])) ++j;
是张(zhang)使(shi)
还是张(zhang)思(si)和(he)
还是藏(zang)和(he)使(shi)
还是藏(zang)和(he)思(si)和(he)呵呵
首字母1----汉字1
首字母1----汉字2
首字母1----汉字2
首字母1----汉字2
首字母1----汉字2
或者做成表就行,两个表,一个是首字母,一个是汉字的编码。反正是1:n的关系
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 * from a where dbo.f_GetPy(Name) like 'ZS%'
FOR INSERT, UPDATE, DELETE
AS
update ccc set pinyin=dbo.F_GetHelpCode(b.name)
from ccc a,inserted b
where a.code=b.code
CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(50) )
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(20), @e VARCHAR(20), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=20 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end
* 获取汉字首字母拼音类
* @author vesoft
*
*/
public class CnStr { private char[] chartable = {
'啊', '芭', '擦', '搭', '蛾', '发', '噶', '哈', '哈',
'击', '喀', '垃', '妈', '拿', '哦', '啪', '期', '然',
'撒', '塌', '塌', '塌', '挖', '昔', '压', '匝', '座'
}; private char[] alphatable = {
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
};
private int[] table = new int[27];
//初始化
{
for (int i = 0; i < 27; ++i) {
table[i] = gbValue(chartable[i]);
}
} //主函数,输入字符,得到他的声母,
//英文字母返回对应的大写字母
//其他非简体汉字返回 '0'
public char Char2Alpha(char ch) {
if (ch >= 'a' && ch <= 'z')
return (char) (ch - 'a' + 'A');
if (ch >= 'A' && ch <= 'Z')
return ch;
int gb = gbValue(ch);
if (gb < table[0])
return '0';
int i;
for (i = 0; i < 26; ++i) {
if (match(i, gb))
break;
}
if (i >= 26)
return '0';
else
return alphatable[i];
} //根据一个包含汉字的字符串返回一个汉字拼音首字母的字符串
public String getAlpha(String SourceStr,int k) {
String Result = "";
int StrLength = SourceStr.length();
int i;
int kk=0;
if(StrLength >k )
kk = k;
else
kk = StrLength;
try {
for (i = 0; i < kk; i++) {
Result += Char2Alpha(SourceStr.charAt(i));
}
} catch (Exception e) {
Result = "";
}
return Result;
} private boolean match(int i, int gb) {
if (gb < table[i])
return false;
int j = i + 1;
//字母Z使用了两个标签
while (j < 26 && (table[j] == table[i]))
++j;
if (j == 26)
return gb <= table[j];
else
return gb < table[j];
} //取出汉字的编码
private int gbValue(char ch) {
String str = new String();
str += ch;
try {
byte[] bytes = str.getBytes("GB2312");
if (bytes.length < 2)
return 0;
return (bytes[0] << 8 & 0xff00) + (bytes[1] &
0xff);
} catch (Exception e) {
return 0;
}}
/**
* 字符转换 转换到国标简体
*/
public String toGB(String strIn) {
String strOut = null;
if (strIn == null || (strIn.trim()).equals("")) {
return "";
}
try {
strOut=new String(strIn.getBytes("GBK"),"GBK");
if(strOut.endsWith(strIn)){
return strIn;
}
strOut = new String(strIn.getBytes("ISO-8859-1"), "GBK");
return (strOut);
} catch (Exception e) {
return "";
}
}// end of UnicodeToGB()
/**
* 获得字符串的ISO8859_1的字节数组
*
* @param s
* @return
*/
public byte[] getISO8859_1(String s) {
if (s == null) {
return null;
} else {
try {
return s.getBytes("ISO8859_1");
} catch (Exception e) {
return null;
}
}
} // 用于处理页内生成的中文数据在写入数据库时的处理,由GBK变为iso8859-1
public String toISO(String s_string) {
try {
String des = new String(s_string.getBytes("GB2312"), "iso8859-1");
return des;
} catch (Exception ex) {
String des = "";
return des;
}
} // 将中英文字串转换成纯英文字串
public String toACSII(String sss) {
StringBuffer sb = new StringBuffer();
byte[] bt = sss.getBytes();
for (int i = 0; i < bt.length; i++) {
if (bt[i] < 0) {
int yy = bt[i];
// 是汉字去高位1
sb.append((char) (yy & 0x7f));
} else {// 是英文字符 补0作记录
sb.append((char) 0);
sb.append((char) bt[i]);
}
}
return sb.toString();
} // 将经转换的字串还原
public String toCH(String ss) {
byte[] bt = ss.getBytes();
int i, l = 0;
int length = bt.length, j = 0;
for (i = 0; i < length; i++) {
if (bt[i] == 0) {
l++;
}
}
byte[] bt2 = new byte[length - l];
for (i = 0; i < length; i++) {
if (bt[i] == 0) {
i++;
bt2[j] = bt[i];
} else {
bt2[j] = (byte) (bt[i] | 0x80);
}
j++;
}
String tt = new String(bt2);
return tt;
} /**
* 转换到unicode编码
*/
public String toGB2312(String strIn1) {
String strOut1 = null;
if (strIn1 == null || (strIn1.trim()).equals("")) {
return "";
}
try {
strOut1 = new String(strIn1.trim().getBytes("GB2312"));
return (strOut1);
} catch (Exception e) {
return "";
}
}// end of ToUnicode() /**
* 转换到BIG
*/
public String toBIG(String strIn2) {
String strOut2 = null;
if (strIn2 == null || (strIn2.trim()).equals("")) {
return null;
}
try {
strOut2 = new String(strIn2.getBytes("GBK"), "BIG5");
return (strOut2);
} catch (Exception e) {
return null;
}
}// end of BIG /***************************************************************************
* 检验一个字符串是否是空值
**************************************************************************/
public static boolean isEmpty(String str) {
if (null == str || ("").equals(str))
return true;
else
return false;
} public String Lock(String strsource) {
String outstr = "";
long a, a1 = 0, a2 = 0, tmp1, tmp2, tt;
String min = "95160";
strsource = toACSII(strsource);
byte[] tmp = strsource.getBytes();
for (int k = 0; k < strsource.length(); k++) {
a = tmp[k];
a1 = a / 4;
a1 = a1 % 16;
tmp1 = a / 64;
tmp2 = a % 4;
a2 = tmp1 * 4 + tmp2;
tt = (Long.parseLong(min) + k) % 2;
if (tt == 1)
a1 = a1 + 16;
else
a2 = a2 + 16;
char u1 = (char) (a1 + 33);
char r1 = (char) (a2 + 61);
outstr = outstr + u1 + r1;
}
return outstr;
} public String unLock(String strsource) {
long a1 = 0, a2 = 0, tmp1, tmp2;
String outstr = "";
byte[] tmp = strsource.getBytes();
for (int k = 0; k < strsource.length() / 2; k++) {
a1 = tmp[k * 2];
a2 = tmp[k * 2 + 1];
a1 = a1 - 33;
a2 = a2 - 61;
if (a1 >= 16)
a1 = a1 - 16;
else
a2 = a2 - 16;
tmp1 = a2 / 4;
tmp2 = a2 % 4;
a2 = tmp1 * 64 + tmp2;
char u1 = (char) (a1 * 4 + a2);
outstr = outstr + u1;
}
return toCH(outstr);
}
public static void main(String[] arg){
//CnStr cs = new CnStr();
try{
//System.out.println(System.getProperty("file.separator")) ;
//System.out.println(System.getProperty("user.dir")) ;
//System.out.println(System.getProperty("path.separator")) ;
}catch(Exception es){
//System.out.println("error" + es.toString()) ;
}
}}
具体的可以看我在这个帖里的回复 http://community.csdn.net/Expert/topic/5411/5411090.xml?temp=.5280878