case sensitive sorting
-
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
-
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?
-
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
-
That works... but that's not without its share of downsides...
-
Yeah I used upper and lower and it works perfect.
Thank you guys
-
I'm not sure I'm following here, what did you do exactly?
-
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
-
How does that fix the sort order?? aside from avoidig the problem?
-
the problem before was that sql server ignores case. when you make everything upper case, evrything is at the same level. it sorts normally.
-
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
-
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
-
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
-
your Q should be another thread, but this article contains some nice refs.
http://qa.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
-
I can see the difference now. Thank you very much ALZDBA. that was really helpful.
Regards
-
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply