case sensitive sorting

Question

Post reply

case sensitive sorting

Bakr Ben Ayad

Old Hand

Points: 325
More actions
March 29, 2007 at 12:08 pm

#116541

Hi
I'm using sql server 2000 and I'm trying to sort an output case sensitively. I mean I want capital letter to come first, then lower case. for example the following data
A789789
a678789
a678799
Is there a way how I can make it sort case sensitively.
Thanks

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That's how close I can get it with the collation sort option.

DECLARE @demo TABLE (id INT NOT NULL IDENTITY(1,1), Name VARCHAR(50))

INSERT INTO @demo (Name) VALUES ('hello')

INSERT INTO @demo (Name) VALUES ('Hello')

INSERT INTO @demo (Name) VALUES ('World')

INSERT INTO @demo (Name) VALUES ('world')

INSERT INTO @demo (Name) VALUES ('wOrld')

SELECT * FROM @demo ORDER BY Name COLLATE LATIN1_GENERAL_CS_AS

Using a case would most likely solve the problem. Do you need a full word case sensitive sort or only the first letter?

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 2

or make the column case sensitive ...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That works... but that's not without its share of downsides...

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 4

Yeah I used upper and lower and it works perfect.

Thank you guys

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I'm not sure I'm following here, what did you do exactly?

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 6

select upper(col_name) as myColName from table order by MyColName

this should do it. If you have any questions, please let me know.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

How does that fix the sort order?? aside from avoidig the problem?

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 8

the problem before was that sql server ignores case. when you make everything upper case, evrything is at the same level. it sorts normally.

mrivera Ten Centuries Points: 1050 More actions · Answer 9

Or, if available, you could use:

SELECT * FROM @demo ORDER BY Name COLLATE SQL_Latin1_General_Pref_CP850_CI_AS

That would give the uppercase preference.

-mr

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 10

maybe clarifies collations and casesensitivity a bit for Bakr Ben Ayad

This was what we were refering to in our replies.

Putting all in uppercase doesn't solve your casesensitive problem, it only converts everything to uppercase. Which would result in the same set as using a case insensitive collation.

Execute this sql-sequence and you'll see the effect ..

(btw it uses only one temp-table (@tmpCaseTest) and doesn't use anything else in your system)

DECLARE @tmpCaseTest TABLE

( IdNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

CIName VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

CSName VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

UpperName VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

SET NOCOUNT ON

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('Alzdba', 'Alzdba', UPPER('Alzdba'))

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('alzdba', 'alzdba', UPPER('alzdba'))

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('ALZDBA', 'ALZDBA', UPPER('ALZDBA'))

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('Alzdba', 'Alzdba', UPPER('Alzdba'))

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('alzdba', 'alzdba', UPPER('alzdba'))

INSERT INTO @tmpCaseTest (CIName, CSName, UpperName) values('ALZDBA', 'ALZDBA', UPPER('ALZDBA'))

SET NOCOUNT OFF

SELECT * FROM @tmpCaseTest ORDER BY CIName ASC ;

-- as suggested by Ninja's_RGR'us

SELECT * FROM @tmpCaseTest ORDER BY CIName COLLATE SQL_Latin1_General_CP1_CS_AS ASC ;

SELECT * FROM @tmpCaseTest ORDER BY CSName ASC ;

SELECT * FROM @tmpCaseTest ORDER BY UPPER(CIName) ASC ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

shiv-356842 SSCarpal Tunnel Points: 4310 More actions · Answer 11

Hi,

What are the basic steps follow to improve the performance of the T-sql command or stored procedure can plz tell me what are the basic steps we should follow.

plz help me

Thanku

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 12

your Q should be another thread, but this article contains some nice refs.

http://www.sqlservercentral.com/columnists/nboyle/speed_select.asp

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 13

I can see the difference now. Thank you very much ALZDBA. that was really helpful.

Regards

mrivera Ten Centuries Points: 1050 More actions · Answer 14

I think there's another issue on this question and it's that the original post was asking for ordering the uppercase letters before the lowercase ones.

That doesn't get solved by using casesensitive collations, since that will take care only of comparisons. The collation necessary here will be one to use uppercase-preference (i.e. SQL_Latin1_General_Pref_Cp850_CI_AS_KI_WI) which will order the records as requested by Bakr Ben Ayad.