Sorting - character after 'Z'?

[Duy Lam]

I'm using collation SQL_Latin1_General_CP1_CI_AS and I need to sort a
varchar field. There are some elements, however, that I want to come
AFTER any alphabetic characters. Is there any character that sorts
after "Z"? In normal ASCII, there are various characters that would
sort after "Z" (e.g. "~"), but I can't seem to figure out what the case
would be in the SQL_Latin1_General_CP1_CI_AS collation. Any ideas?

thanks,
Duy Lam

[Simon Hayes]

"Duy Lam" <du****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

I'm using collation SQL_Latin1_General_CP1_CI_AS and I need to sort a
varchar field. There are some elements, however, that I want to come
AFTER any alphabetic characters. Is there any character that sorts
after "Z"? In normal ASCII, there are various characters that would
sort after "Z" (e.g. "~"), but I can't seem to figure out what the case
would be in the SQL_Latin1_General_CP1_CI_AS collation. Any ideas?

thanks,
Duy Lam

Perhaps you can post a specific script to show what you want? Many
characters might sort after 'Z', as you said - maybe you should check out
ASCII(), CHAR(), UNICODE(), COLLATE and "Using Unicode Data" in Books
Online? If you can post something that others can copy and paste into Query
Analyzer, then there's a better chance that you'll get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[Duy Lam]

Well, it's not so much there's a specific script I have that I need to
get working. I just want a way to enforce a certain order among rows.
For instance, let's say I have a table

T(someindex int, sortkey varchar(5), val int)

....and it's loaded with some data. for each row, the "sortkey" is a
field so that a user (who's using my app) can manually enforce an order
among "val"s. Normally it will be filled with some kind of alphanumeric
data.

I want to return the "val" column, but ordered by "sortkey". This, of
course, is easily obtained by running:

SELECT val FROM T ORDER BY sortkey

but for some rows, I may want to force certain "val"s to appear at the
end. One way, of course, is to set sortkey to "ZZZZZ" for those vals,
but there might be a chance that "ZZZZZ" is an actual value set by the
user. Thus I want some kind of character that is sorted even lower than
"Z" to guarantee that it comes after any possible alphanumeric entry
for "sortkey".

as i mentioned, in ASCII there are a number of characters that follow
'z', but I don't know any for the standard SQL_Latin1_General_CP1_CI_AS
collation.

[Erland Sommarskog]

[posted and mailed, please reply in news]

Duy Lam (du****@gmail.com) writes:

I'm using collation SQL_Latin1_General_CP1_CI_AS and I need to sort a
varchar field. There are some elements, however, that I want to come
AFTER any alphabetic characters. Is there any character that sorts
after "Z"? In normal ASCII, there are various characters that would
sort after "Z" (e.g. "~"), but I can't seem to figure out what the case
would be in the SQL_Latin1_General_CP1_CI_AS collation. Any ideas?

Looks like you have to learn Icelandic. :-) The script below helps you
out:

CREATE TABLE slafs (a int IDENTITY,
b AS char(a),
c int NOT NULL)
go
INSERT slafs (c) SELECT TOP 255 OrderID FROM Northwind..Orders
go
SELECT * FROM slafs ORDER BY b
SELECT * FROM slafs ORDER BY b COLLATE SQL_Latin1_General_CP1_CI_AS
go
DROP TABLE slafs
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp