怎么在数据库中按汉字首位字母取出数据～～～
比如下表：
id name
1 波
2 病
3 啊
4 吹
5 崔
如果按字母A取出数据就是：
id name
2 啊
按B是：
id name
1 波
2 病
按C是：
4 吹
5 崔
各位大哥帮我看看怎么弄。
[解决办法]
把数据库数据载入到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 HZ2PYgoCreate Function HZ2PY( @cString nVarChar (200))Returns nVarChar(100)ASBegin 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 @vReturnEnddeclare @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 @tb
[解决办法]
create function fnGetPy(@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @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
