Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

what comes after 'Z'? (for varchar order by)

I've been hunting around for a character to put at the beginning of a varchar field that would cause it to alphabetize at the end of the list in an order by.  The field is defined as:
[MyField] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

And everything I have tried that comes after 'z' (lower case, which comes after upper case using an ascii table) gets sorted to the top instead of the bottom.  This includes the tilde, and broken and unbroken pipe character, both curly braces, and a couple of extended set characters way down on the list.

Thanks for any help-
0
ottenm
Asked:
ottenm
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Hmm, odd, those apparently should work.

If you don't care what the char looks like, I suggest CHAR(255).
0
 
ottenmAuthor Commented:
I thought so, too.  

The 255 works, but it is pretty ugly (hence the attempts with tilde, etc.).  I could code around it, but would prefer to find out why it's happening.  Maybe something with the COLLATE attribute?
0
 
Scott PletcherSenior DBACommented:
True, it's ugly.

You could make the main query a sub-query, with a TOP 100 PERCENT to allow the ORDER BY in the subquery, then do a replace in an outer query to get rid of the CHAR(255) -- yuck.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now