Order by hierarchical substring?-CodePudding

I use SQL Server. I have columns in my table like so:

title:
1. Album one
2. Album two
2.1 - Song one
2.2 - Song two
2.10 - Song ten

I want to order my query by the first part of the string (the structure). If I order it now the result is the following:

select * from [albums] order by [albums].[title]

/* Result */

title:
1. Album one
2. Album two
2.1 - Song one
2.10 - Song ten /* notice this */
2.2 - Song two

How can I cast my string so I can order correctly?

Update:

I can have multiple/unknown levels of nesting (2.2.1.1, 2.2.1.2, 2.2.2, 2.2.3)

CodePudding user response：

One option is a CROSS APPLY to grab the desired portion of the string

Select [Title]
 From  YourTable A
 Cross Apply ( values ( left([Title],charindex(' ',[Title])) ) ) B(Seq)
 Order by try_convert(int,parsename(Seq,2))
         ,try_convert(int,parsename(Seq,1))

Results

Title
1. Album one
2. Album two
2.1 - Song one
2.2 - Song two
2.10 - Song ten

EDIT For multi Level

Select [Title]
 From YourTable A
 Cross Apply ( values  ( try_convert(hierarchyid,'/' replace(left([Title],charindex(' ',[Title])),'.','/') '/' ))) B(Seq)
 Order by Seq

CodePudding user response：

You can do

SELECT *
FROM albums
ORDER BY HierarchyId::Parse('/'   
                      TRIM ('.' FROM LEFT(title, CHARINDEX(' ', title)-1))   
                      '/')

This extracts the value before the first space as the value to sort, strips any trailing . from it (as is present in 1. and 2.) then uses the HIERARCHYID datatype to provide sorting with the semantics you desire.

(DB Fiddle)