Group By and Ignore Case

[22]

Is it possible to build a select query that groups by a value, but ignores the
character case of the source values? For example, GROUP BY that searches a
column for 'test" and "TEST" would combine the two values instead of grouping
them separately.

[paross1]

Different databases have different function names for converting to upper or
lower case (ucaselcase in Access, upper/lower in Oracle and MSSQL, etc.) When
you use GROUP BY, you need to use the same columns (without the aggregrate
column) that you use in the SELECT, so if you wish to do a caseless group by on
a column, you would need to convert the column in the select as well as in the
group by.

For example,

SELECT UPPER(last_name) AS UClast_name, COUNT(*) AS user_count
FROM yourtable
GROUP BY UPPER(last_name)

would give you a count by last name, regardless of the case. However, as you
can see, your SELECTed value is now upper case, which may not be what you want
to see in your output.

Phil

[22]

I used the Lcase() function in cfquery, but it only works in a direct query. I was trying to use it in the query of queries.