SQL Sorting By String That Contains WBS

I had an issue where we had strings that contained a wbs number in front of it. Not all of the strings contained a wbs however. I was trying to sort the strings correctly if they contained a wbs number. The wbs would be a list like this:

1.1,1.2,1.3,1.4,1.5,1.6,...,1.11

This list should put the 1.11 after 1.10 and 1.9, however, it was putting the item after 1.1 if I just sorted by the name.

My solution is listed below. It will first check if the first character is a number. If it isn't a number, it will add the ascii characters (since
the case statement requires an integer in this case) for the first two characters in the string and sort by that.

If it does contain a number, then I assume that it is a wbs number. I also assume that the wbs number will not go 3 levels deep. If that is the case, this solution won't work. This solution will only work if the wbs number is 2 levels deep and you don't need to alpha sort past 2 characters in the name.

If it has a wbs number, I will parse it up and sort on the first number, and then the second number.

Usually you would want an order id in the database to sort it correctly, but in this case we didn't have that option.

SELECT distinct
case isnumeric(substring(deliverablename,0,2)) when 1
then cast(substring(deliverablename,0,2) as int) else cast(ascii(deliverablename) as int)+
cast(ascii(substring(deliverablename,1,2)) as int) end as nodecimal ,
case isnumeric(substring(deliverablename,0,2)) when 1 then
cast(substring(deliverablename,0,CHARINDEX('.',deliverablename,1)) as int)
else cast(ascii(deliverablename) as int)+ cast(ascii(substring(deliverablename,1,2)) as int)
end as firstdecimal , case isnumeric(substring(deliverablename,0,2)) when 1 then
cast(substring(deliverablename,CHARINDEX('.',deliverablename,1)+1
,CHARINDEX(' ',deliverablename,1)-CHARINDEX('.',deliverablename,1)) as int)
else cast(ascii(deliverablename) as int)+ cast(ascii(substring(deliverablename,1,2)) as int)
end as seconddecimal, userdefinedfixedfeeid,budgetfixedfeedate,deliverablename,
budgetfixedfee.fixedfeeid,deliverableamount FROM FixedFeeSchedule INNER JOIN
BudgetFixedFee ON FixedFeeSchedule.FixedFeeID = BudgetFixedFee.FixedFeeId
INNER JOIN RevisionHistory ON BudgetFixedFee.BudgetId = RevisionHistory.BudgetId
AND RevisionHistory.RevisionNum = 0 INNER JOIN Budget ON FixedFeeSchedule.CustomerId =
Budget.CustomerId AND FixedFeeSchedule.EngagementID = Budget.EngagementId AND
BudgetFixedFee.BudgetId = Budget.BudgetId INNER JOIN Project ON FixedFeeSchedule.CustomerId =
Project.CustomerId AND Budget.ProjectId = Project.ProjectId left outer JOIN Tasks
ON Project.CustomerId = Tasks.CustomerId AND Project.EngagementId = Tasks.EngagementId AND
Project.ProjectId = Tasks.ProjectId AND BudgetFixedFee.FixedFeeId = Tasks.FixedFeeId and
tasks.deleted=0 left outer JOIN TaskAssignment ON Tasks.TaskId = TaskAssignment.TaskId
AND Tasks.CustomerId = TaskAssignment.CustomerId AND Tasks.EngagementId = TaskAssignment.
EngagementId AND Tasks.ProjectId = TaskAssignment.ProjectId and TaskAssignment.deleted=0
left outer JOIN Resources ON TaskAssignment.ResourceId = Resources.ResourceId left outer
join resourcerate rr on rr.resourceid=taskassignment.resourceid and rr.active=1 and
rr.effectivedate=(select isnull(max(rrra.effectivedate),0) from ResourceRate rrra
INNER JOIN Resources rcas ON rrra.ResourceId = rcas.ResourceId and rcas.resourceid=
rr.resourceid and rrra.active=1) WHERE (BudgetFixedFee.CopyTo IS NULL) AND
(FixedFeeSchedule.Deleted = 0) AND (BudgetFixedFee.Deleted = 0)
AND (Budget.ProjectId = '{307B2997-5F01-4E27-8E6B-25BEC20EAF27}')
AND (Budget.Deleted = 0) order by case isnumeric(substring(deliverablename,0,2))
when 1 then cast(substring(deliverablename,0,2) as int) else cast(ascii(deliverablename) as int)
+ cast(ascii(substring(deliverablename,1,2)) as int) end,
case isnumeric(substring(deliverablename,0,2)) when 1 then
cast(substring(deliverablename,0,CHARINDEX('.',deliverablename,1)) as int)
else cast(ascii(deliverablename) as int)+ cast(ascii(substring(deliverablename,1,2)) as int)
end, case isnumeric(substring(deliverablename,0,2)) when 1 then
cast(substring(deliverablename,CHARINDEX('.',deliverablename,1)+1,
CHARINDEX(' ',deliverablename,1)-CHARINDEX('.',deliverablename,1)) as int)
else cast(ascii(deliverablename) as int)+ cast(ascii(substring(deliverablename,1,2)) as int)
end