Order by in SQL when the values have String and Numbers

Question

SQL Server 2012

I have values in 'name' column like this

AMOU
VHOR
1A
7W1
11W
12W

and I am using this query to sort them in order

select * from Unit c where expsetid=382 order by CONVERT(INT,substring(name, 0, PATINDEX('%[^0-9]%', name+'z'))) asc

When I order by desc I got

12W
11W
7W1
1A
AMOU
VHOR

But I expect them to be in this order when its desc

12W
11W
7W1
1A
VHOR
AMOU

@DourHighArch the link you gave its not behaving as expected eigther — Niamath
– Niamath, Commented Apr 11, 2014 at 21:49

muhmud · Accepted Answer · 2014-04-11 22:16:24Z

1

Your using 0 as the start index for substring, which should instead be 1.

When you use zero, you actually get an empty string that is being converted to 0 and thus ordering the alpha strings first. When you use 1, you correctly get an conversion to int error.

answered Apr 11, 2014 at 22:16

muhmud

4,6242 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Niamath Over a year ago

I got error when I used '1' "Conversion failed when converting the nvarchar value '11W' to data type int."

Niamath · Accepted Answer · 2014-04-15 15:15:04Z

0

Thank you all for helping me in finding the solution as I expected. Here is the query which work for my situation.

select * from Unit c 
where expsetid=382
order by Row_Number() over (Order By CONVERT(INT,substring(name, 0, PATINDEX('%[^0-9]%', name+'z')))) desc

answered Apr 15, 2014 at 15:15

Niamath

3094 silver badges12 bronze badges

Collectives™ on Stack Overflow

Order by in SQL when the values have String and Numbers

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related