community.borland.com

Article #25222: Sorting Names Using Character Set - Collation Column/Table Definition

Problem:
Consider the situation where you wish to create a table that's going to contain names of persons 
that might have a combination of uppercase and lowercase characters.  You would like to get 
an ordered listing of these names,  ignoring the different cases of characters.  However, since the 
ascii values of the character will be considered, the SQL query will list out the names in an order 
where the names with upper case come first.
 For Example :
=============
create database "coldef.gdb";
create table colnames  (
 lname varchar(20),
 fname varchar(20)
  )
SQL> select * from colnames order by lname;
LNAME                FNAME                
========          ============= 
Briggs                 leilani              
Buttle                   James                
Wu                        tai                  
brown                  glen                 
little                      Dale                 
roche                   Michelle   
======================================

Solution:
To solve this problem and to avoid a painful workaround of having an extra column that converts all 
characters to the same case, choose the right character set and collation order. 
 For Example:
   create collate.gdb;
   create table colnames  (
      lname varchar(20) character set ISO8859_1 collate EN_US,
      fname varchar(20) character set ISO8859_1 collate EN_US );
SQL> select * from colnames order by lname; 
LNAME                                   	FNAME               
 ===================== ==================== 
Briggs                                  	leilani              
brown                                  	glen                 
Buttle                                  	James                
little                                     	Dale                 
roche                                  	Michelle             
Wu                                      	tai                  
==========================================

Last Modified: 02-OCT-00