日期:2014-05-18  浏览次数:20943 次

怎么在数据库中按汉字首位字母取出数据~~~
比如下表:
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版的提取首字母的函数,这个可能对部分生僻字无效,但是大多都是没问题的
VB.NET code

    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

------解决方案--------------------
SQL code
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