这里用到几个函数,一个SQL自定义函数,主要按汉语拼音字母排序,这个函数来源于网上收集,记不得作者是谁了,还有那个取汉字首字母(大写),首先在这里感谢他们。
Create function GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
然后在查询进,用这样SQL语句
Select Id,Title From Table_a order by dbo.getpy(title)
这样做后TITLE已经按汉语拼音字母顺序排列了,
还有一个取汉字取拼音首字母(大写) 函数
1
Public Shared Function Extract_HZ(ByVal HZ As String) As String
2
Dim ZW(2) As Byte
3
Dim HZ_INT As Long
4
ZW = System.Text.Encoding.Default.GetBytes(HZ)
5
Dim i1 As Integer = CType((ZW(0)), Short)
6
Dim i2 As Integer = CType((ZW(1)), Short)
7
HZ_INT = i1 * 256 + i2
8
If (HZ_INT >= 45217) AndAlso (HZ_INT <= 45252) Then
9
Return "A"
10
End If
11
If (HZ_INT >= 45253) AndAlso (HZ_INT <= 45760) Then
12
Return "B"
13
End If
14
If (HZ_INT >= 45761) AndAlso (HZ_INT <= 46317) Then
15
Return "C"
16
End If
17
If (HZ_INT >= 46318) AndAlso (HZ_INT <= 46825) Then
18
Return "D"
19
End If
20
If (HZ_INT >= 46826) AndAlso (HZ_INT <= 47009) Then
21
Return "E"
22
End If
23
If (HZ_INT >= 47010) AndAlso (HZ_INT <= 47296) Then
24
Return "F"
25
End If
26
If (HZ_INT >= 47297) AndAlso (HZ_INT <= 47613) Then
27
Return "G"
28
End If
29
If (HZ_INT >= 47614) AndAlso (HZ_INT <= 48118) Then
30
Return "H"
31
End If
32
If (HZ_INT >= 48119) AndAlso (HZ_INT <= 49061) Then
33
Return "J"
34
End If
35
If (HZ_INT >= 49062) AndAlso (HZ_INT <= 49323) Then
36
Return "K"
37
End If
38
If (HZ_INT >= 49324) AndAlso (HZ_INT <= 49895) Then
39
Return "L"
40
End If
41
If (HZ_INT >= 49896) AndAlso (HZ_INT <= 50370) Then
42
Return "M"
43
End If
44
If (HZ_INT >= 50371) AndAlso (HZ_INT <= 50613) Then
45
Return "N"
46
End If
47
If (HZ_INT >= 50614) AndAlso (HZ_INT <= 50621) Then
48
Return "O"
49
End If
50
If (HZ_INT >= 50622) AndAlso (HZ_INT <= 50905) Then
51
Return "P"
52
End If
53
If (HZ_INT >= 50906) AndAlso (HZ_INT <= 51386) Then
54
Return "Q"
55
End If
56
If (HZ_INT >= 51387) AndAlso (HZ_INT <= 51445) Then
57
Return "R"
58
End If
59
If (HZ_INT >= 51446) AndAlso (HZ_INT <= 52217) Then
60
Return "S"
61
End If
62
If (HZ_INT >= 52218) AndAlso (HZ_INT <= 52697) Then
63
Return "T"
64
End If
65
If (HZ_INT >= 52698) AndAlso (HZ_INT <= 52979) Then
66
Return "W"
67
End If
68
If (HZ_INT >= 52980) AndAlso (HZ_INT <= 53640) Then
69
Return "X"
70
End If
71
If (HZ_INT >= 53689) AndAlso (HZ_INT <= 54480) Then
72
Return "Y"
73
End If
74
If (HZ_INT >= 54481) AndAlso (HZ_INT <= 55289) Then
75
Return "Z"
76
End If
77
' Return ("")
78
Return (UCase(Left(HZ, 1)))
79
End Function
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](/Images/OutliningIndicators/InBlock.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/InBlock.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/InBlock.gif)
24
![](/Images/OutliningIndicators/InBlock.gif)
25
![](/Images/OutliningIndicators/InBlock.gif)
26
![](/Images/OutliningIndicators/InBlock.gif)
27
![](/Images/OutliningIndicators/InBlock.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/InBlock.gif)
31
![](/Images/OutliningIndicators/InBlock.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/InBlock.gif)
34
![](/Images/OutliningIndicators/InBlock.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/InBlock.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/InBlock.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/InBlock.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/InBlock.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/InBlock.gif)
59
![](/Images/OutliningIndicators/InBlock.gif)
60
![](/Images/OutliningIndicators/InBlock.gif)
61
![](/Images/OutliningIndicators/InBlock.gif)
62
![](/Images/OutliningIndicators/InBlock.gif)
63
![](/Images/OutliningIndicators/InBlock.gif)
64
![](/Images/OutliningIndicators/InBlock.gif)
65
![](/Images/OutliningIndicators/InBlock.gif)
66
![](/Images/OutliningIndicators/InBlock.gif)
67
![](/Images/OutliningIndicators/InBlock.gif)
68
![](/Images/OutliningIndicators/InBlock.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/InBlock.gif)
71
![](/Images/OutliningIndicators/InBlock.gif)
72
![](/Images/OutliningIndicators/InBlock.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/InBlock.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/InBlock.gif)
77
![](/Images/OutliningIndicators/InBlock.gif)
78
![](/Images/OutliningIndicators/InBlock.gif)
79
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
接着写一个函数接入字母分隔线
1 Private Function DownListInsertZM(ByVal DropDownListId As DropDownList, ByVal InsertHH As String) As DropDownList
2 Dim j, s, i, c, r As Integer
3 Dim tempdual As String
4 For c = 65 To 90 '大写ASCII码循环
5 i = 0
6 For j = 0 To DropDownListId.Items.Count - 1
7 tempdual = Extract_HZ(DropDownListId.Items(j).Text).Trim
8 If tempdual = Chr(c) Then
9 i = i + 1 '统计相同项数
10 s = j
11 End If
12 Next
13 r = s - i + 1 '计算插入位置的索引号
14 If r < 0 Then
15 r = 0
16 End If
17 If i <> 0 Then
18 DropDownListId.Items.Insert(r, New ListItem((InsertHH & Chr(c) & InsertHH), "*"))
19 End If
20 Next
21 DropDownListId.Items.Insert(0, New ListItem((InsertHH & "请选择" & InsertHH))) '插入"请选择"为Dropdownlist为第一项
22 End Function
23
2 Dim j, s, i, c, r As Integer
3 Dim tempdual As String
4 For c = 65 To 90 '大写ASCII码循环
5 i = 0
6 For j = 0 To DropDownListId.Items.Count - 1
7 tempdual = Extract_HZ(DropDownListId.Items(j).Text).Trim
8 If tempdual = Chr(c) Then
9 i = i + 1 '统计相同项数
10 s = j
11 End If
12 Next
13 r = s - i + 1 '计算插入位置的索引号
14 If r < 0 Then
15 r = 0
16 End If
17 If i <> 0 Then
18 DropDownListId.Items.Insert(r, New ListItem((InsertHH & Chr(c) & InsertHH), "*"))
19 End If
20 Next
21 DropDownListId.Items.Insert(0, New ListItem((InsertHH & "请选择" & InsertHH))) '插入"请选择"为Dropdownlist为第一项
22 End Function
23
最后便可实现如下效果:
---请选择---
----A----
啊
爱国者
----B----
变速器
................
笔者后来想了一下,也可以在DataTable实现这个操作,因我是用DataReader的方法读数据,所在后面这个插入分隔线的操作应该在数据绑定之后再进行,如果在DATATABLE中就插好了,可以直接绑定。待有时间写个DataTable的。小弟第一次发文章,请各位指教。