把数据库数据载入到datatable,然后写个函数来提取汉字字段的的首字母加到datatable的新建列中 然后用datatable的select来对新建列进行筛选~下面是vb.net版的提取首字母的函数,这个可能对部分生僻字无效,但是大多都是没问题的 Public Function Getpychar(ByVal char1) Dim tmp As Long tmp = 65536 + Asc(char1) If (tmp >= 45217 And tmp <= 45252) Then Return "A" ElseIf (tmp >= 45253 And tmp <= 45760) Then Return "B" ElseIf (tmp >= 45761 And tmp <= 46317) Then Return "C" ElseIf (tmp >= 46318 And tmp <= 46825) Then Return "D" ElseIf (tmp >= 46826 And tmp <= 47009) Then Return "E" ElseIf (tmp >= 47010 And tmp <= 47296) Then Return "F" ElseIf (tmp >= 47297 And tmp <= 47613) Then Return "G" ElseIf (tmp >= 47614 And tmp <= 48118) Then Return "H" ElseIf (tmp >= 48119 And tmp <= 49061) Then Return "J" ElseIf (tmp >= 49062 And tmp <= 49323) Then Return "K" ElseIf (tmp >= 49324 And tmp <= 49895) Then Return "L" ElseIf (tmp >= 49896 And tmp <= 50370) Then Return "M" ElseIf (tmp >= 50371 And tmp <= 50613) Then Return "N" ElseIf (tmp >= 50614 And tmp <= 50621) Then Return "O" ElseIf (tmp >= 50622 And tmp <= 50905) Then Return "P" ElseIf (tmp >= 50906 And tmp <= 51386) Then Return "Q" ElseIf (tmp >= 51387 And tmp <= 51445) Then Return "R" ElseIf (tmp >= 51446 And tmp <= 52217) Then Return "S" ElseIf (tmp >= 52218 And tmp <= 52697) Then Return "T" ElseIf (tmp >= 52698 And tmp <= 52979) Then Return "W" ElseIf (tmp >= 52980 And tmp <= 53688) Then Return "X" ElseIf (tmp >= 53689 And tmp <= 54480) Then Return "Y" ElseIf (tmp >= 54481 And tmp <= 62289) Then Return "Z" Else '如果不是中文,则不处理 Getpychar = char1 End If End Function Public Function Getpy(ByVal str) As String Dim temp As String temp = "" Dim i As Integer For i = 1 To Len(str) temp = temp & Getpychar(Mid(str, i, 1)) Next Return temp End Function
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 declare @tb table (id int,name varchar(20)) insert into @tb select 1,'波' insert into @tb select 2,'病' insert into @tb select 3,'啊' insert into @tb select 4,'吹' insert into @tb select 5,'崔'select *,dbo.hz2py(name) as '首字母' from @tbid name 首字母 1 波 B 2 病 B 3 啊 A 4 吹 C 5 崔 C
create function fnGetPy(@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 dbo.fnGetPy('首字母')另外SQL Server还可以写个DLL在SP中调用...ps:14:28请记得默哀3分钟...
1 波 b
2 病 b
3 啊 a
4 吹 c
5 崔 c然后再处理
然后用datatable的select来对新建列进行筛选~下面是vb.net版的提取首字母的函数,这个可能对部分生僻字无效,但是大多都是没问题的 Public Function Getpychar(ByVal char1)
Dim tmp As Long
tmp = 65536 + Asc(char1)
If (tmp >= 45217 And tmp <= 45252) Then
Return "A"
ElseIf (tmp >= 45253 And tmp <= 45760) Then
Return "B"
ElseIf (tmp >= 45761 And tmp <= 46317) Then
Return "C"
ElseIf (tmp >= 46318 And tmp <= 46825) Then
Return "D"
ElseIf (tmp >= 46826 And tmp <= 47009) Then
Return "E"
ElseIf (tmp >= 47010 And tmp <= 47296) Then
Return "F"
ElseIf (tmp >= 47297 And tmp <= 47613) Then
Return "G"
ElseIf (tmp >= 47614 And tmp <= 48118) Then
Return "H"
ElseIf (tmp >= 48119 And tmp <= 49061) Then
Return "J"
ElseIf (tmp >= 49062 And tmp <= 49323) Then
Return "K"
ElseIf (tmp >= 49324 And tmp <= 49895) Then
Return "L"
ElseIf (tmp >= 49896 And tmp <= 50370) Then
Return "M"
ElseIf (tmp >= 50371 And tmp <= 50613) Then
Return "N"
ElseIf (tmp >= 50614 And tmp <= 50621) Then
Return "O"
ElseIf (tmp >= 50622 And tmp <= 50905) Then
Return "P"
ElseIf (tmp >= 50906 And tmp <= 51386) Then
Return "Q"
ElseIf (tmp >= 51387 And tmp <= 51445) Then
Return "R"
ElseIf (tmp >= 51446 And tmp <= 52217) Then
Return "S"
ElseIf (tmp >= 52218 And tmp <= 52697) Then
Return "T"
ElseIf (tmp >= 52698 And tmp <= 52979) Then
Return "W"
ElseIf (tmp >= 52980 And tmp <= 53688) Then
Return "X"
ElseIf (tmp >= 53689 And tmp <= 54480) Then
Return "Y"
ElseIf (tmp >= 54481 And tmp <= 62289) Then
Return "Z"
Else '如果不是中文,则不处理
Getpychar = char1
End If
End Function Public Function Getpy(ByVal str) As String
Dim temp As String
temp = ""
Dim i As Integer
For i = 1 To Len(str)
temp = temp & Getpychar(Mid(str, i, 1))
Next
Return temp
End Function
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
declare @tb table (id int,name varchar(20))
insert into @tb select 1,'波'
insert into @tb select 2,'病'
insert into @tb select 3,'啊'
insert into @tb select 4,'吹'
insert into @tb select 5,'崔'select *,dbo.hz2py(name) as '首字母' from @tbid name 首字母
1 波 B
2 病 B
3 啊 A
4 吹 C
5 崔 C
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 dbo.fnGetPy('首字母')另外SQL Server还可以写个DLL在SP中调用...ps:14:28请记得默哀3分钟...