How to Make ORDER BY case insensitive? 1

[flies4fun]

The following query causes the company names to be returned sorted in alphabetical order but with lower case coming after upper case. i.e. Castrol coming after COMPMore. How do I make the ORDER BY ignore case and sort just by the letter? I've tried an UPPER(company) in the SELECT and while that works, it changes the company name to all caps for the rest of the application.
Thanks.

<CFQUERY NAME=&quot;companies&quot; DATASOURCE=&quot;customers&quot;>
SELECT DISTINCT company
FROM database
WHERE (company LIKE 'C%') OR (company LIKE 'c%')
ORDER BY company
</CFQUERY>

 

[Silvano]

I am not quite sure I understand what you are looking for, but this is the query I used:

<cfquery name=&quot;allTables&quot; datasource=&quot;dbn&quot; dbtype=&quot;ODBC&quot;>
SELECT user_table_name
FROM admin_tables
WHERE user_table_name LIKE 'B%'
ORDER BY user_table_name;
</cfquery>

And this is the output I've got:

Background
banner
Bullet
Button

sorted just by the letter, right?

 

[tleish]

What you could do is select the field &quot;company&quot; twice, once for viewing and a second time for simply ordering. The second time selecting it you change it to upper(), give it a new name like &quot;key&quot;, and then ORDER BY key.

<CFQUERY NAME=&quot;companies&quot; DATASOURCE=&quot;customers&quot;>
SELECT company, upper(company) as key
FROM database
WHERE (company LIKE 'C%') OR (company LIKE 'c%')
ORDER BY key
</CFQUERY> - tleish

 

[flies4fun]

I'm using DISTINCT because the same company name appears multiple times in the db and I need a count of companies. When I tried the sylvano's suggestion, I don't get the records that start with 'c' and the list looks like:
CISCO
CITIBANK
Castleman
Caterpillar
etc.
Perhaps because it's Foxpro data?

tleish's solution worked great. Thanks to you both.

 

[Silvano]

I am using Microsoft Access, and yes, while it has some restrictions regarding naming tables, it is not case sensitive; in other words, if I ask for all fields that start with &quot;c&quot; it will return all fields that start with &quot;c&quot; or &quot;C&quot;; once I have query results, I can sort them in whatever way I want.
Maybe your next step should be to check case sensitivity of your database