Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
340 views
in Technique[技术] by (71.8m points)

sql server - Sort Bullets in Database

I have a column [datatype:varchar(50)] in database (SQL Server 2008) having Values as shown below:

1
2
1.1.11
4.1
5
2.1
1.1
4
1.2.1
4.2.2
4.3
4.2
4.3.1
4.2.1
11.2
1.2.4
4.4

these are numbered bullets for my records I need to sort them as grouping all the records in sequence 1,1.1,1.1.1,2,3.1,4,10.1,11.1....

Please help me in this regard.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
WITH T(YourColumn) AS
(
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '1.1.11' UNION ALL
SELECT '4.1' UNION ALL
SELECT '5' UNION ALL
SELECT '2.1' UNION ALL
SELECT '1.1' UNION ALL
SELECT '4' UNION ALL
SELECT '1.2.1' UNION ALL
SELECT '4.2.2' UNION ALL
SELECT '4.3' UNION ALL
SELECT '4.2' UNION ALL
SELECT '4.3.1' UNION ALL
SELECT '4.2.1' UNION ALL
SELECT '11.2' UNION ALL
SELECT '1.2.4' UNION ALL
SELECT '4.4'
)
SELECT *
FROM T 
ORDER BY CAST('/' + YourColumn + '/' AS HIERARCHYID)

Returns

YourColumn
----------
1
1.1
1.1.11
1.2.1
1.2.4
2
2.1
4
4.1
4.2
4.2.1
4.2.2
4.3
4.3.1
4.4
5
11.2

Is that what you need?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...