CODEWITHSUNDEEP
sql-server
Itecor Durango
Itecor Durango

Reputation: 13

Sort alphanumeric in SQL Server

I need help to order the next statement:

select numbering from QuestionnaireQuestion where  
QuestionnaireQuestion.questionnaire_id = 20 AND 
    QuestionnaireQuestion.numbering is not null
    order by LEFT(numbering,PATINDEX('%[0-9]%',numbering)-1)

the result is:

Q2
Q2.a
Q2.b
Q3
Q4
Q4.a
Q5
Q6
Q6.a
Q6.a.1
Q6.a.2
Q6.a.3
Q6.a.4
Q7
Q8
Q8.a
Q9
Q10
Q10.a
Q10.b
Q11
Q11.a
Q12
Q12.a
Q12.b
Q13
Q13.a
Q13.a.1
Q13.a.2
Q13.a.3
Q13.a.4
Q13.a.5
Q13.a.6
Q13.a.7
Q13.a.8
Q13.b
Q13.b.1
Q13.b.2
Q13.b.3
Q13.b.4
Q13.b.5
Q13.b.6
Q13.b.7
Q13.b.8
Q1

The last is Q1, but i need to Q1 in the TOP

Upvotes: 0

Views: 51

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

The real challenge here is that your are storing multiple pieces of information in a single tuple. This violates 1NF and is truly awful to work with. Since you have a varying number of elements this is even more difficult. Assuming you never exceed 3 elements you can use some ugly string manipulation as a hack to make this work. Please note the performance of this is not going to scale well but that is because the design is less than optimal.

declare @Something table (SomeValue varchar(20))

insert @Something (SomeValue) values
('Q2')
, ('Q2.a')
, ('Q2.b')
, ('Q3')
, ('Q4')
, ('Q4.a')
, ('Q5')
, ('Q6')
, ('Q6.a')
, ('Q6.a.1')
, ('Q6.a.2')
, ('Q6.a.3')
, ('Q6.a.4')
, ('Q7')
, ('Q8')
, ('Q8.a')
, ('Q9')
, ('Q10')
, ('Q10.a')
, ('Q10.b')
, ('Q11')
, ('Q11.a')
, ('Q12')
, ('Q12.a')
, ('Q12.b')
, ('Q13')
, ('Q13.a')
, ('Q13.a.1')
, ('Q13.a.2')
, ('Q13.a.3')
, ('Q13.a.4')
, ('Q13.a.5')
, ('Q13.a.6')
, ('Q13.a.7')
, ('Q13.a.8')
, ('Q13.b')
, ('Q13.b.1')
, ('Q13.b.2')
, ('Q13.b.3')
, ('Q13.b.4')
, ('Q13.b.5')
, ('Q13.b.6')
, ('Q13.b.7')
, ('Q13.b.8')
, ('Q1')
, ('Q11')


select *

from @Something s
order by reverse(PARSENAME(reverse(s.SomeValue), 1))
    , reverse(PARSENAME(reverse(s.SomeValue), 2))
    , reverse(PARSENAME(reverse(s.SomeValue), 3))
    --If you have 4 elements you can add one more level. 
    --But this will not work if you exceed 4 elements because of how PARSENAME works.
    --, reverse(PARSENAME(reverse(s.SomeValue), 4))

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

I believe you need the substring, not the left

order by substring(numbering,PATINDEX('%[0-9]%',numbering),100)

however this will not solve for 10 and above, but the following will:

select numbering , right('0000'+substring(numbering,pos1,pos2-pos1),4)
from QuestionnaireQuestion 
cross apply (
      select PATINDEX('%[0-9]%',rtrim(numbering)), PATINDEX('%[.]%',rtrim(numbering)+'.')
   ) ca (pos1, pos2)
order by right('0000'+substring(numbering,pos1,pos2-pos1),4), numbering

It "left pads" the first number found e.g. 6 becomes 0006 or 91 become 0091 and so on.

The cross apply is used to calculate 2 positions in the string, where the first number is found, and where the first period is found (& note a period is added to ensure this is always positive). From these positions the first number is isolated and then concatenated to '0000' eg. 6 becomes 00006 or 91 becomes 000091, then only the right 4 chars are used so we get 0001 .... 9999 possibilities.

dbfiddle demo here

Upvotes: 1

Related Questions