RE: Sort (Collating Sequence)

[Mercadante, Thomas F]

Title: Sort (Collating Sequence)

Laura,

 

The way that I have always handled this was to create another column in the table, and populate it with a before insert/update trigger.

 

In your case, I would create a VENDOR_SORT column and move the vendor_name column into this column.  I have created a function to strip out all non-alpha fields (like single quotes, hyphens, spaces etc) that would screw up the sort order.  This way, the VENDOR_SORT field always will return things in the correct order.  Things like McDonald and Mc Donald, O'Reilly and OReilly, as well as te' Reille and Terelle would all come out together where they belong.

 

hope this helps.

 

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 23, 2003 4:25 PM
To: Multiple recipients of list ORACLE-L
Subject: Sort (Collating Sequence)

I have a question concerning a situation with our ORDER BY clauses.  We have a vendor table which allows the user to input any case.  Therefore we have 'Vendor' and 'VENDOR'.  When using the ORDER BY clause it sorts VENDOR first and then Vendor.  I need for the names to be sorted regardless of the capitalization.  I know that we could have put an UPPER function on the input of this data to alleviate this problem, but the deed is done.  I had suggested using the UPPER in the ORDER BY clause to always insure true alphabetizing but the thought was to have the database handle this instead of relying on the application.

I have found SQLCASE which works when I SELECT but not on the ORDER BY clause.  Plus this is SQL*Plus only.  I have researched the NLS parameters and read about binary sorts vs linguistic sorts as well as the different parameters available, but I did not see anything that could handle this situation systemically.  I basically wanted to see how to add UPPER to an ORDER BY clause without having to actually code it.

Does anyone know if there is such a creature?  My boss says that SQL Server has an option to do this, which immediately puts me on the defense and retort (in good humor of course) that I was sure Oracle did if Microsoft did!!  I do not mind researching but I do not know anywhere else to look. 

Thanks in advance for your replies,

Laura