Sorting: Letters before numbers

Question

Sorting: Letters before numbers

Maxime.Gagne

SSC Eights!

Points: 886
More actions
October 11, 2011 at 12:59 pm

#244361

Hello,
I would like to sort letters before numbers. A letter can appear anywere within the varchar and must be evaluated before numbers.
create table #tempSort
(
MerchantID varchar(16),
POS varchar(2),
Amount int
)
INSERT into #tempSort
VALUES ('ABC4567812345678', Null, Null);
INSERT into #tempSort
VALUES ('ABC456789BC45678', Null, Null);
INSERT into #tempSort
VALUES ('1234567812345678', Null, Null);
INSERT into #tempSort
VALUES ('12345678ABC45678', Null, Null);
INSERT into #tempSort
VALUES ('ABC45678Z3Y45678', Null, Null);
INSERT into #tempSort
VALUES ('ABC4D678ZAY45678', Null, Null);
INSERT into #tempSort
VALUES ('A4C4A678ZXY45678', Null, Null);
INSERT into #tempSort
VALUES ('ABC4D678ABD45678', Null, Null);
INSERT into #tempSort
VALUES ('A2C4A678B3A45678', Null, Null);
INSERT into #tempSort
VALUES ('12C4A678ZXY45678', Null, Null);
SELECT MerchantID from #tempSort
ORDER BY MerchantID
This will give me this outcome:
1234567812345678
12345678ABC45678
1234A678B3A45678
12A4D678ABD45678
12C4A678ZXY45678
A2C4A678B3A45678
A4C4A678ZXY45678
ABC4567812345678
ABC456789BC45678
ABC45678Z3Y45678
ABC4D678ABD45678
ABC4D678ZAY45678
What I'm looking for is the following outcome:
ABC4D678ABD45678 -- 1st Pos: A < 1, 2nd Pos: B < 2, 4th pos: D < 5, 9th Pos: A < Z
ABC4D678ZAY45678 -- 1st Pos: A < 1, 2nd Pos: B < 2, 4th pos: D < 5
ABC45678Z3Y45678 -- 1st Pos, A < 1, 2nd Pos: B < 2, 9th Pos: Z < 1
ABC4567812345678 -- 1st Pos, A < 1, 2nd Pos: B < 2, 9th Pos: 1 < 9
ABC456789BC45678 -- 1st Pos, A < 1, 2nd Pos: B < 2,
A2C4A678B3A45678 -- 1st Pos, A < 1, 2nd Pos: 2 < 4
A4C4A678ZXY45678 -- 1st Pos, A < 1
12A4D678ABD45678 -- 3rd Pos: A < C
12C4A678ZXY45678 -- 3rd Pos: C < 3
1234A678B3A45678 -- 5th Pos: A < 5
12345678ABC45678 -- 9th Pos: A < 1
1234567812345678
Thanks for your help,

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Add Desc after merchantid in your order by clause

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Maxime.Gagne SSC Eights! Points: 886 More actions · Answer 2

Wow, can't believe it was that simple.

Guess I overlooked some basics and dug too deep.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Maxime.Gagne SSC Eights! Points: 886 More actions · Answer 4

It worked fine on the test sample, but when applying this to my real situation, it did not workout well.

Using DESC on the ORDER BY now evaluates the letters in descending order also, therefore Z comes before A.

Letters must be evaluated in Ascending order AND be evaluated before numbers.

ex:

Let's add MerchantID 'ABC45678ZXY4567' to our test sample,

Using Desc will give the following outcome:

ABC45678ZXY4567

ABC45678ABC4567

ABC456781234567

12345678ABC4567

123456781234567

But desired outcome is:

ABC45678ABC4567

ABC45678ZXY4567

ABC456781234567

12345678ABC4567

123456781234567

Lowell SSC Guru Points: 323507 More actions · Answer 5

how about this?

SELECT MerchantID from #tempSort

ORDER BY CASE

WHEN LEFT(MerchantID,1) LIKE '%[A-Z]%'

THEN 1

ELSE 2

END,MerchantID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Try this

SELECT MerchantID

from #tempSort

ORDER BY LEFT(merchantID,8) DESC,CASE WHEN ISNUMERIC(REVERSE(right(REVERSE(right(merchantid,LEN(MerchantID)-8)),3))) = 1 THEN 1 ELSE 0 end

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 7

SQLRNNR (10/11/2011)
Try this
SELECT MerchantID
from #tempSort
ORDER BY LEFT(merchantID,8) DESC,CASE WHEN ISNUMERIC(REVERSE(right(REVERSE(right(merchantid,LEN(MerchantID)-8)),3))) = 1 THEN 1 ELSE 0 end

Oh... be careful, Jason. REVERSE is terribly expensive compared to other methods especially in things like ORDER BY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Maxime.Gagne SSC Eights! Points: 886 More actions · Answer 8

I tried the reverse solution but it did not work out as it is also ordering letters on descending order.

I updated my original post with a more accurate query of what I'm trying to do with an explanation of the desired result.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 9

A bit obscure but try this

SELECT MerchantID from #tempSort

ORDER BY MerchantID COLLATE SQL_EBCDIC037_CP1_CS_AS

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Maxime.Gagne SSC Eights! Points: 886 More actions · Answer 10

It is indeed obscure, never heard of this SQL Collection but it works!

I'll do some homework and research that collection.

Thanks a lot

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Maxime.Gagne (10/12/2011)
I tried the reverse solution but it did not work out as it is also ordering letters on descending order.
I updated my original post with a more accurate query of what I'm trying to do with an explanation of the desired result.

Wow, that is far different than what you originally posted. I had a revised query but it won't do you any good.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Jeff Moden (10/12/2011)
SQLRNNR (10/11/2011)
Try this
SELECT MerchantID
from #tempSort
ORDER BY LEFT(merchantID,8) DESC,CASE WHEN ISNUMERIC(REVERSE(right(REVERSE(right(merchantid,LEN(MerchantID)-8)),3))) = 1 THEN 1 ELSE 0 end
Oh... be careful, Jason. REVERSE is terribly expensive compared to other methods especially in things like ORDER BY.

The best thing to do with this kind of data, requirement, and design is to redesign it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Maxime.Gagne (10/12/2011)
It is indeed obscure, never heard of this SQL Collection but it works!
I'll do some homework and research that collection.
Thanks a lot

It's actually COLLATION. This is your best bet without redesigning the database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nwerner Right there with Babe Points: 798 More actions · Answer 14

It's not that obscure, although it is probably obsolete. IBM mainframes had the EBCDIC sort sequence where letters sort before numerals instead of / prior to ASCII. Watch out for other printable characters however! Some appear in the middle of the alphabet. This is from SAS (r) language documentation:

EBCDIC Order

The z/OS operating environment uses the EBCDIC collating sequence.

The sorting order of the English-language EBCDIC sequence is

blank . < ( + | & ! $ * ) ; ¬ - / , % _ > ?: # @ ' = "

a b c d e f g h i j k l m n o p q r ~ s t u v w x y z

{ A B C D E F G H I } J K L M N O P Q R \S T

U V W X Y Z 0 1 2 3 4 5 6 7 8 9

-----------------------------------------------------------------

ASCII Order

The operating environments that use the ASCII collating sequence include

UNIX and its derivatives

OpenVMS

Windows.

From the smallest to the largest character that you can display, the English-language ASCII sequence is

blank ! " # $ % & ' ( ) * + , - . /0 1 2 3 4 5 6 7 8 9 : ; < = > ? @

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z[ \] ˆ_

a b c d e f g h i j k l m n o p q r s t u v w x y z { } ~