Re: HELP: CASE SENSITIVE OREDRING IN SELECT

Operator (nariani@server1.teachersoft.com) wrote: : I tried tp post this article last week, but was unsuccessful : in doing so. : I want to write a SELECT statement using ORDER BY textcolumn. : The results I get give me case sensitive sorting i.e. Z is less than a. : How can I get the results sorted irrespective of the case? : I am using Informix 5.02 UC6 on SunOS 4.1.3. : Please copy the repolies to nariani@teachersoft.com : -Sunil I can think of two solutions to your problem, depending upon whether or not you have the ability to manage the table that you're querying. In both cases, I assume you are using a programming language that has a function to convert to uppercase (e.g., ESQL/C has "rupshift"). Neither is an ideal solution. 1. If you can create or alter the table and you are using a programming language that has a function to convert to uppercase (e.g., ESQL/C has "rupshift"), then create a column that stores the data all in uppercase. Rebuild the table so that as records are added, the desired string is stored both in its natural form and as uppercase characters. Then in your SELECT, ORDER BY the uppercase field. 2. If you can only access the table, SELECT the desired field and place in a TEMP table. Then open the TEMP for modification and convert the data in each record to uppercase. Finally, SELECT all records in the TEMP and ORDER BY that field. I hope this information helps you. Sorry, I don't know what to do if you're just writing SQL (say, via "dbaccess"). Informix can be a nuisance at times, can't it? ------------ Curtis Kling NEC America, Irving TX kling@esd.dl.nec.com