---测试数据--- if object_id('[pactinfo]') is not null drop table [pactinfo] go create table [pactinfo]([ID] int,[pactname] varchar(4)) insert [pactinfo] select 1,'正常' union all select 2,'中国' union all select 3,'做饭' union all select 4,'加发'
---引用前辈们的一个函数--- 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 ---查询--- select * from [pactinfo] where left(dbo.f_GetPy(pactname),1)='Z'---结果--- ID pactname ----------- -------- 1 正常 2 中国 3 做饭(所影响的行数为 3 行)
可以增加一个用户定义的函数,创建的sql如下:CREATE function fun_getPY(@str nvarchar(4000)) returns nvarchar(4000) as begin set @str=replace(replace(replace(@str,' ',''),'(',''),')','') 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 然后在写sql语句的时候,比如要查询'客户资料'表中'姓名'这一项是不是以z开头的客户,可以这样写语句:select 姓名 from 客户资料 where dbo.fun_getPY(姓名) like 'z%',应该可以满足你的需求了吧.
http://topic.csdn.net/t/20041024/19/3486485.html
里面cnming(cnming)老大给的方法
楼主可以参考哦。
if object_id('[pactinfo]') is not null drop table [pactinfo]
go
create table [pactinfo]([ID] int,[pactname] varchar(4))
insert [pactinfo]
select 1,'正常' union all
select 2,'中国' union all
select 3,'做饭' union all
select 4,'加发'
---引用前辈们的一个函数---
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
---查询---
select
*
from
[pactinfo]
where
left(dbo.f_GetPy(pactname),1)='Z'---结果---
ID pactname
----------- --------
1 正常
2 中国
3 做饭(所影响的行数为 3 行)
2 在数据库中汉字要转成拼音吧,并且一样对应起来,这个你可以用查到Imegen.exe命令,将系统带的输入法的编码导出看看
returns nvarchar(4000)
as
begin
set @str=replace(replace(replace(@str,' ',''),'(',''),')','')
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 然后在写sql语句的时候,比如要查询'客户资料'表中'姓名'这一项是不是以z开头的客户,可以这样写语句:select 姓名 from 客户资料 where dbo.fun_getPY(姓名) like 'z%',应该可以满足你的需求了吧.
发下
//我编程时要用到查询测字拼音首字母的功能,于是从网上搜
//但发现大部分都是将汉字的内码转换成区位码,然后得出拼音的首字母
//这个方法好是好,只是许多GB2312编码以外的汉字查不到,于是我想了这个办法,我觉得代码很短,但很实用
//而且GBK字符集的汉字都能查,希望大家多提意见
public class hzpy
{
public hzpy()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//返回给定字符串的单个字
public String IndexCode(String IndexTxt)
{
String _Temp=null;
for(int i=0;i<IndexTxt.Length;i++)
_Temp = _Temp + GetOneIndex( IndexTxt.Substring(i,1) );
return _Temp;
} //得到单个字符的首字母
private String GetOneIndex(String OneIndexTxt)
{
if(Convert.ToChar(OneIndexTxt)>=0 && Convert.ToChar(OneIndexTxt)<256)
return OneIndexTxt;
else
return GetGbkX(OneIndexTxt);
} //根据根据测字拼音排序得到首字母 private string GetGbkX(string str)
{
if(str.CompareTo("吖")<0)
{
return str;
}
if(str.CompareTo("八")<0)
{
return "A";
} if(str.CompareTo("嚓")<0)
{
return "B";
} if(str.CompareTo("咑")<0)
{
return "C";
}
if(str.CompareTo("妸")<0)
{
return "D";
}
if(str.CompareTo("发")<0)
{
return "E";
}
if(str.CompareTo("旮")<0)
{
return "F";
}
if(str.CompareTo("铪")<0)
{
return "G";
}
if(str.CompareTo("讥")<0)
{
return "H";
}
if(str.CompareTo("咔")<0)
{
return "J";
}
if(str.CompareTo("垃")<0)
{
return "K";
}
if(str.CompareTo("嘸")<0)
{
return "L";
}
if(str.CompareTo("拏")<0)
{
return "M";
}
if(str.CompareTo("噢")<0)
{
return "N";
}
if(str.CompareTo("妑")<0)
{
return "O";
}
if(str.CompareTo("七")<0)
{
return "P";
}
if(str.CompareTo("亽")<0)
{
return "Q";
}
if(str.CompareTo("仨")<0)
{
return "R";
}
if(str.CompareTo("他")<0)
{
return "S";
}
if(str.CompareTo("哇")<0)
{
return "T";
}
if(str.CompareTo("夕")<0)
{
return "W";
}
if(str.CompareTo("丫")<0)
{
return "X";
}
if(str.CompareTo("帀")<0)
{
return "Y";
}
if(str.CompareTo("咗")<0)
{
return "Z";
}
return str;
} }