CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Administrateur
Administrateur

Reputation: 901

SQL - Order by with alphanumeric characters

I have the following table:

create table #tbl
(
 product_id nvarchar(50)
)

insert into #tbl values ('011014-A11')
insert into #tbl values ('011014-A10')
insert into #tbl values ('011014')
insert into #tbl values ('011014-A1')
insert into #tbl values ('011014-A2')

I want to order the Product IDs like this (from the smallest to the greatest):

011014
011014-A1
011014-A2
011014-A10
011014-A11

This is what I tried, but it is not working:

select product_id
from #tbl
order by product_id desc

How do I do this?

And how do I get just the greatest product_id? I tried this, but it didn't work:

select top 1 product_id
from #tbl
order by product_id desc

Upvotes: 3

Views: 1188

Answers (2)

D Stanley
D Stanley

Reputation: 152491

You have already accepted Kaf's answer, and it may work well for you, but it will only work as long as the first 8 characters are the same. For example if you added a value of 011014-B1 then your results would be:

011014
011014-A1
011014-A2
011014-B1
011014-A10
011014-A11

If your pattern is consistent (sort by the first8 chars then by the "number" after them, this may work better for you:

Select product_id
From #tbl              
Order by LEFT(product_id,8), CAST(SUBSTRING(product_id,9,9) AS INT)

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Try following query to get the results in your expected order.

Fiddle demo

Select product_id
From tbl              --Please change table name to #tbl in your actual query
Order by len(product_id), product_id

Upvotes: 2

Related Questions