CODEWITHSUNDEEP
sql-servert-sql
Ismail Rajah
Ismail Rajah

Reputation: 11

alphanumeric sorting in sql

Hi I am currently doing a project in which the database needs to sort the lot numbers

prefix is nvarchar
lotnum is int
suffix is nvarchar

I have managed to convert the lot number code i used is

Select (case when prefix is null then '' else prefix end) +
CONVERT ( nvarchar , ( lotnumber ) ) +(case when suffix is null then '' else suffix end)
(values in the database are a1a,1a,1,2,100)

when I order by lotnumber I get

a1a
1a
1
2
100

then prefix to the order by and get this result

1
a1a
1a
2
100

I have added the suffix as well and returns the same result

I need to order it as follows

1
1a
2
100
a1a

Please could someone help me on this

Upvotes: 1

Views: 707

Answers (1)

Diego
Diego

Reputation: 7572

Have you tried ordering by all three columns?

ORDER BY prefix, lotnum, suffix

By the way, I can see you're using SQL Server. To make things more portable, I'd recommend to use COALESCE and CAST instead of a CASE/WHEN and CONVERT for prefix and lotnum. Full query may look like this.

SELECT
  COALESCE(prefix, '')
  + CAST(lotnum AS NVARCHAR)
  + COALESCE(suffix, '') AS lot_number
FROM
  YourTable
ORDER BY
  COALESCE(prefix, '')
  ,lotnum
  ,COALESCE(suffix, '')

Upvotes: 3

Related Questions