先维护好一个汉字首字母的字段,查询的时候直接查这个字段.汉字转拼音的函数: If Exists(Select * From sysobjects T Where T.id = object_id(N'HZ2PY') AND xtype IN(N'FN', N'IF', N'TF')) drop Function HZ2PY go Create Function HZ2PY(@cString nVarChar (200)) Returns nVarChar(100) AS Begin Declare @nFor Int --字符串循环用 Declare @nPYFor Int --拼音串循环用 Declare @cPYString VarChar(100) --拼音串 Declare @cCurChar VarChar(2) --当前字符 Declare @vReturn VarChar (100) ----将返回的结果 Set @cPYString = '吖八嚓咑妸发旮铪讥讥咔垃呣拿讴趴七呥仨他哇哇哇夕丫匝咗' Set @vReturn = '' Set @nFor = 0 While @nFor < Len(@cString) Begin Set @nFor = @nFor + 1 Set @cCurChar = Substring(@cString,@nFor,1) Set @nPYFor = 0 While @nPYFor < len(@cPYString) Begin Set @nPYFor = @nPYFor + 1 If @cCurChar < Substring(@cPYString,@nPYFor,1) Begin Set @vReturn = Rtrim(@vReturn) + (Case When @nPYFor <> 1 Then Char(63 + @nPYFor) Else @cCurChar End) Break End Else Begin Continue End End End Return @vReturn End
If Exists(Select * From sysobjects T Where T.id = object_id(N'HZ2PY') AND xtype IN(N'FN', N'IF', N'TF'))
drop Function HZ2PY
go
Create Function HZ2PY(@cString nVarChar (200))
Returns nVarChar(100)
AS
Begin
Declare @nFor Int --字符串循环用
Declare @nPYFor Int --拼音串循环用
Declare @cPYString VarChar(100) --拼音串
Declare @cCurChar VarChar(2) --当前字符
Declare @vReturn VarChar (100) ----将返回的结果
Set @cPYString = '吖八嚓咑妸发旮铪讥讥咔垃呣拿讴趴七呥仨他哇哇哇夕丫匝咗'
Set @vReturn = ''
Set @nFor = 0
While @nFor < Len(@cString)
Begin
Set @nFor = @nFor + 1
Set @cCurChar = Substring(@cString,@nFor,1)
Set @nPYFor = 0
While @nPYFor < len(@cPYString)
Begin
Set @nPYFor = @nPYFor + 1
If @cCurChar < Substring(@cPYString,@nPYFor,1)
Begin
Set @vReturn = Rtrim(@vReturn) + (Case When @nPYFor <> 1 Then Char(63 + @nPYFor) Else @cCurChar End)
Break
End
Else
Begin
Continue
End
End
End
Return @vReturn
End
/*
------------------------
BGYP (所影响的行数为 1 行)
*/
select * from 表 where 助记码='bgyp'