Sort alphanumeric values

Transact-SQL
1

Hi ,

I would like to sort varchar values alpha and then numeric
Like if caracter in the string is a number in one value and a letter in the other value at the same position,
it should order by letter fisrt

Like those 2 values :
PTQHGD0107T
PTQHGD01CA8

Should be sorted to give:
PTQHGD01CA8 -- the letter C before the number 7
PTQHGD0107T

and also....
Like those 2 values :
GMNRON08DS0
GMNRON07ES0

Should be sorted to give:
GMNRON07ES0 -- The number 7 before the number 8
GMNRON08DS0

and finally....
Like those 2 values :
GMNRON08KS0
GMNRON08ES0

Should be sorted to give:
GMNRON08ES0 -- The letter E before the letter K
GMNRON08KS0

Thanks!

2

Are the alphas and numbers always in fixed positions? Otherwise, this will be very complicated and have a lot of overhead to do.

3

Hi,

The values always starts with 6 letters but then after it can be any combinations of letters and numbers.

4

This appears to work:

create table #(a varchar(30) not null unique);
insert # values('GMNRON07ES0'),('GMNRON08DS0'),('GMNRON08ES0'),('GMNRON08KS0'),('PTQHGD0107T'),('PTQHGD01CA8')

select * from # order by TRANSLATE(a,'abcdefghijklmnopqrstuvwxyz0123456789','000000000000000000000000009999999999'), a
5

Hi robert_volk,

The built-in function Translate does not exist in SQL 2014 :smiling_face_with_tear:

6

I came to this solution that works. It is ugly but it works! :slight_smile:

Select TrackNumber from tbl_orders
order by 
   PATINDEX('%[0-9]%', substring(TrackNumber ,1,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,2,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,3,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,4,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,5,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,6,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,7,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,8,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,9,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,10,1))
  ,PATINDEX('%[0-9]%', substring(TrackNumber ,11,1))
7

That won't guarantee sort order for your last example:

GMNRON08ES0 -- The letter E before the letter K
GMNRON08KS0

8

hi does this work for you ?

select TrackNumber from tbl_orders order by BINARY_CHECKSUM(TrackNumber)

image
image755×367 12.5 KB

image
image735×373 12.3 KB

9

I think this works, and it seems more accurate than the TRANSLATE solution I posted earlier;

create table #(a varchar(30) not null unique);
insert # values('GMNRON07ES0'),('GMNRON08DS0'),('GMNRON08ES0'),('GMNRON08KS0'),('PTQHGD0107T'),('PTQHGD01CA8')
,('PTQHGD01AA8'),('PTQHGD01BA8'),('PTQHGD01DA8'),('PTQHGDD1EA8'),('PTQHGD01ZA8')

select * from # 
order by 
left(a,6)  -- get the prefix sorted first
, ~(cast(substring(a,7,8) as varbinary(8)) & cast(0x4040404040404040 as bigint)) -- order with digits sorting last
, a  -- order by actual value

The 2nd ORDER BY expression does some bitwise operations to mark letters vs. digits, then inverts/negates the bits to sort characters first. I've added a few additional test cases and they seem to sort correctly, but it's not exhaustive.

This will fail if your codes are longer than 14 characters long, you'd have too section the code into 8 byte chunks and do multiple bitwise ops. It also won't sort correctly if the first 6 characters contain a digit.

10

Or just order by an EBCDIC collation. eg

ORDER BY TrackNumber COLLATE SQL_EBCDIC1141_CP1_CS_AS

ie In ASCII numbers come before letters but in EBCDIC numbers come after letters.

ps I am not sure what EBCDIC collations are in SQL2014 but they can be found with:

SELECT [name], [Description]
FROM sys.fn_helpcollations()
WHERE [name] LIKE '%EBCDIC%';
1 Like
11

Hi Ifor,

Your solution works likle a charm. I thank you very much.
and thanks everyone else for spending time to help.