如果只是英文和数字的可以试试下面的方法:
在ORACLE SQL中有这样一个函数ASCII(char)
ASCII(char)的参数可以为字符串
例如:
select ASCII('D'), ASCII('Dave') FROM dual
返回为:
ASCII('D') ASCII('Dave')
---------------------------
68 68
==========================================================================
比如说我们要查到指定字段第一个字是以韵母“L”开头的，我们可以用以下SQL语句进行查询：
SELECT * FROM 表名 WHERE author 所要查询的字段>='垃' AND 所要查询的字段 <'妈'
这个语句就能查询出“所要查询的字段”的第一个字以“L”开头的所要数据。
这查询方法的依据是某一个韵母在新华字典中的首个汉字和下一个韵母的第一个汉字作为查询的条件，进行查询。如果查询的时候要包含英文字母，可以使用下面的语句：
SELECT * FROM 表名 WHERE author 所要查询的字段 LIKE 'L' OR ( 所要查询的字段>='垃' AND 所要查询的字段 <'妈')
特别说明一下当要查询“Z”的时候，查询的条件是“ >='杂' OR <'坐' ”
下面是韵母和汉字的对照表：
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: 杂
这个是我的项目中用到的一个例子，不知道是否是最佳的，若朋友们有更好的方法，还请赐教！
-按26个字母搜索
if(@key<>'')
begin--0
if(@key='A')
begin--1
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='吖' and movename<'巴') or movename like 'A%')
end--1
else if(@key='B')
begin--2
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='巴' and movename<'擦') or movename like 'B%')
end--2
else if(@key='C')
begin--3
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='擦' and movename<'搭') or movename like 'C%')
end--3
else if(@key='D')
begin--4
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='搭' and movename<'鹅') or movename like 'D%')
end--4
else if(@key='E')
begin--5
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='仨' and movename<'他') or movename like 'E%')
end--5
-------------------------
else if(@key='F')
begin--6
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='发' and movename<'旮') or movename like 'F%')
end--6
else if(@key='G')
begin--7
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='旮' and movename<'哈') or movename like 'G%')
end--7
else if(@key='H')
begin--8
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='哈' and movename<'鸡') or movename like 'H%')
end--8
else if(@key='I')
begin--9
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and movename like 'I%'
end--9
else if(@key='J')
begin--10
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='鸡' and movename<'喀') or movename like 'J%')
end--10
---------------------------------------------------------
else if(@key='K')
begin--11
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='喀' and movename<'垃') or movename like 'K%')
end--11
else if(@key='L')
begin--12
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='垃' and movename<'妈') or movename like 'L%')
end--12
else if(@key='M')
begin--13
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='妈' and movename<'嗯') or movename like 'M%')
end--13
else if(@key='N')
begin--14
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='嗯' and movename<'哦') or movename like 'N%')
end--14
else if(@key='O')
begin--15
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='哦' and movename<'趴') or movename like 'O%')
end--15
---------------------------------------------
else if(@key='P')
begin--16
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='趴' and movename<'欺') or movename like 'P%')
end--16
else if(@key='Q')
begin--17
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='欺' and movename<'然') or movename like 'Q%')
end--17
else if(@key='R')
begin--18
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='然' and movename<'仨') or movename like 'R%')
end--18
else if(@key='S')
begin--19
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='仨' and movename<'他') or movename like 'S%')
end--19
else if(@key='T')
begin--20
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='他' and movename<'挖') or movename like 'T%')
end--20
---------------------------------------------------
else if(@key='U')
begin--21
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and movename like 'U%'
end--21
else if(@key='V')
begin--22
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and movename like 'V%'
end--22
else if(@key='W')
begin--23
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='挖' and movename<'西') or movename like 'W%')
end--23
else if(@key='X')
begin--24
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='西' and movename<'压') or movename like 'X%')
end--24
else if(@key='Y')
begin--25
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='压' and movename<'杂') or movename like 'Y%')
end--25
else if(@key='Z')
begin--26
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='杂' and movename<'坐') or movename like 'Z%')
end--26
end--0
else
begin--0-1
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0)
end--0-1
=========================================================================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 order by case Name when '其他 ' then 1 else 0 end,dbo.f_GetPy(Name)
