关于SQL查询数据库中以第一个相同拼音开头字符问题，各位DX看过来，急！

[disha8888]

现在有这样一个表 USER的表，表中有[USERNAME] [USERINFO] [EMAIL]等字段

我想做一个这样的查询，但不知怎么做合适，类似于FOXMAIL5.0中地址簿这种查询

比如以字每A字开头的可以查询出来大小写的字母及以A字拼音做开头的数据
Alex
abu
aband
阿伟
阿康
安小然
......

查询B字每开头的可以查出来
Balack
benyond
冰小雪
贝莼

[disha8888]

该怎么查询呀，各位DX请帮下忙，谢谢！

[pengdali]

转
--可支持大字符集20000个汉字!

create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63

select @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
go

select dbo.f_ch2py('中') --Z
select dbo.f_ch2py('国') --G
select dbo.f_ch2py('人') --R
select dbo.f_ch2py('镆') --M
-----------------调用
CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(20) )
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=12 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
GO

--调用
select dbo.F_GetHelpCode('大力')



--你的：
select * from 表 where dbo.F_GetHelpCode(列) like 'a%'

[CrazyFor]

disha8888(disha8888) ,
create function .....开头到
....
end
go

之间的代码只要运行一次就行了,不需要多次运行.这是建立自定义函数,建好就直接用就行了.

[disha8888]

thanks 大力，已搞定，呵呵

谢谢大家了

[leimin]

'******自定义函数，对任意输入的汉字，可以得到它的拼音的第一个字母********
'//函数入口为汉字串，返回值为该汉字的第一个字母
Public Function getHzPy(hzStr As String) As String
On Error Resume Next
'declare variable
Dim myHzm As Integer
Dim qm As Integer
Dim wm As Integer
Dim hznm As String
If Len(hzStr) > 1 Then
myHzm = Asc(Left(hzStr, 1))
Else
myHzm = Asc(hzStr)
End If
If myHzm >= 0 And myHzm < 256 Then
'字母
getHzPy = hzStr
Else
'汉字
qm = (myHzm + 65536) \ 256 '取区码
wm = (myHzm + 65536) Mod 256 '取位码
'十进制到十六进制
hznm = tento(qm, 16) & tento(wm, 16)
End If
If "B0A1" <= hznm And hznm <= "B0C4" Then
getHzPy = "A"
ElseIf "B0C5" <= hznm And hznm <= "B2C0" Then
getHzPy = "B"
ElseIf "B2C1" <= hznm And hznm <= "B4ED" Then
getHzPy = "C"
ElseIf "B4EE" <= hznm And hznm <= "B6E9" Then
getHzPy = "D"
ElseIf "B6EA" <= hznm And hznm <= "B7A1" Then
getHzPy = "E"
ElseIf "B7A2" <= hznm And hznm <= "B8C0" Then
getHzPy = "F"
ElseIf "B8C1" <= hznm And hznm <= "B9FD" Then
getHzPy = "G"
ElseIf "B9FE" <= hznm And hznm <= "BBF6" Then
getHzPy = "H"
ElseIf "BBF7" <= hznm And hznm <= "BFA5" Then
getHzPy = "J"
ElseIf "BFA6" <= hznm And hznm <= "C0AB" Then
getHzPy = "K"
ElseIf "C0AC" <= hznm And hznm <= "C2E7" Then
getHzPy = "L"
ElseIf "C2E8" <= hznm And hznm <= "C4C2" Then
getHzPy = "M"
ElseIf "C4C3" <= hznm And hznm <= "C5B5" Then
getHzPy = "N"
ElseIf "C5B6" <= hznm And hznm <= "C5BD" Then
getHzPy = "O"
ElseIf "C5BE" <= hznm And hznm <= "C6D9" Then
getHzPy = "P"
ElseIf "C6DA" <= hznm And hznm <= "C8BA" Then
getHzPy = "Q"
ElseIf "C8BB" <= hznm And hznm <= "C8F5" Then
getHzPy = "R"
ElseIf "C8F6" <= hznm And hznm <= "CBF9" Then
getHzPy = "S"
ElseIf "CBFA" <= hznm And hznm <= "CDD9" Then
getHzPy = "T"
ElseIf "CDDA" <= hznm And hznm <= "CEF3" Then
getHzPy = "W"
ElseIf "CEF4" <= hznm And hznm <= "D188" Then
getHzPy = "X"
ElseIf "D1B9" <= hznm And hznm <= "D4D0" Then
getHzPy = "Y"
ElseIf "D4D1" <= hznm And hznm <= "D7F9" Then
getHzPy = "Z"
Else
getHzPy = hznm
End If
End Function
'************************辅助函数，可以从十进制转换到任意进制**********************
'//入口为十进制数，要转换的进制，返回为该进制数
Public Function tento(m As Integer, n As Integer) As String
Dim q As Integer
Dim r As Integer
tento = ""
Dim bStr As String
Do
Call myDivide(m, n, q, r)
If r > 9 Then
bStr = Chr(55 + r)
Else
bStr = Str(r)
End If
tento = Trim(bStr) & tento
m = q
Loop While q <> 0
End Function

'************************辅助过程，得到任意两个数的商和余数***************************
Public Sub myDivide(num1 As Integer, num2 As Integer, q As Integer, r As Integer)
If num2 = 0 Then
MsgBox ("非法除数")
Exit Sub
End If
If num1 / num2 >= 0 Then
q = Int(num1 / num2)
Else
q = Int(num1 / num2) + 1
End If
r = num1 Mod num2
End Sub

[zjcxc]

--这个函数可以直接返回指定字符串(可以是字母,中文字符的组合)的拼音:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetPy]
GO

--创建取拼音函数
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @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 '丌' --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 '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
else set @c=@chn
set @return=@return+@c
end
return(@return)
end

go
--测试
select dbo.fgetpy('东莞市') as 东莞市,dbo.fgetpy('abc中国人') as 中国人

--删除拼音函数
drop function fgetpy

[disha8888]

to pengdali(大力 V3.0)

对我来说有点太复杂了，我在SQL中的查询分析器中试了一下出错说

服务器: 消息 2714，级别 16，状态 5，过程 f_ch2py，行 42
数据库中已存在名为 'f_ch2py' 的对象。
服务器: 消息 111，级别 15，状态 1，行 7
'CREATE FUNCTION' 必须是批查询中的第一条语句。
服务器: 消息 137，级别 15，状态 1，行 14
必须声明变量 '@cName'。
服务器: 消息 137，级别 15，状态 1，行 15
必须声明变量 '@cname'。
服务器: 消息 178，级别 15，状态 1，行 25
在此上下文中不能使用带有返回值的 RETURN 语句。
服务器: 消息 208，级别 16，状态 1，行 7
对象名 'ABO.F_GetHelpCode' 无效。

---------------------------------------
另外，如果放在ASP中该怎么调用呀，谢谢！

[gmlxf]

你在查询分析器中执行
CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(20) )
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=12 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
GO

然后在asp中
set rs=conn.execute("select * from 表 where dbo.F_GetHelpCode(列) like 'a%')
就可以了

[gmlxf]

这两个函数真的很不错，我也收藏一下，谢谢大力！！