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