代码人生的小狗窝

一行行枯燥的代码,却描绘出人生的点点滴滴

您现在的位置是:首页>_C#

如何在数据库中按汉字首位字母取出数据~

发布时间:2019-09-19浏览(1070)

    怎么在数据库中按汉字首位字母取出数据~~~
    比如下表:
    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
          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 @tb