Sort ASC where UPPERCASE = lowercase

[dreamingdigital]

I want to sorc ascending but I want UPPERCASE and lowercase treated equally.

Example:
Currently it results like
1
2
3
A
B
C
a
b
c

I want the sort to be like
1
2
3
A or a
B or b
C or c

This is because some people spell their name with a capital letter first and some don't. This makes my sorting ugly because the people who spelled their name in all lowercase have their name at the end of the list.

[CFMaBiSmAd]

ORDER BY UPPER(your_column)

[dreamingdigital]

I just want people who type in their name in all lowercase letters to be sorted properly along with the people who Capitalize the first letter of their name.

That didn't work. Here is my code:

<cfquery dbtype="query" name="registrant2">
select *
from registrant
order by firstname asc
</cfquery>

I've tried UPPER like

<cfquery dbtype="query" name="registrant2">
select *
from registrant
order by firstname upper
</cfquery>

and

<cfquery dbtype="query" name="registrant2">
select *
from registrant
order by UPPER(firstname)
</cfquery>

but no luck. I just get a big Cold Fusion error page.

[Old Pedant]

What kind of database are you using???

UPPER(fieldname) is the proper answer for MySQL, but the answer is different for other database systems. (UPPER should also work with SQL Server. Access would use UCASE(fieldname) and there are other variations possible with other DBs.)

And you know, when you get an error message, it would help *TONS* if you would copy/paste the ENTIRE error message here. Even if you don't understand what a given error message means, chances are that somebody in these forums will.

[dreamingdigital]

Thanks. The DB is MySQL. via ColdFusion. I'm learning here.

OK, I've tried a bunch of permutations and from this link (http://forums.adobe.com/thread/86245) I should be able to use the upper() function.

Here's my code: (the variable #sort_results_by# equals "FIRSTNAME" but can equal whatever I pass)

<cfset sort_results_by = URL.sort_my_results/>
<cfquery dbtype="query" name="registrant2">
select *
from registrant
order by UPPER(#sort_results_by#) asc
</cfquery>


and here is the error:

Error Executing Database Query.

Query Of Queries syntax error.
Encountered "UPPER. Incorrect ORDER BY column reference [UPPER].
Only simple column reference, alias name, and integer column id are allowed.
Example: You can use alias to refer to a complex expression:
SELECT (a+b)/2 as x FROM T ORDER BY x

The error occurred in colin.cfm: line 33

31 : select *
32 : from registrant
33 : order by UPPER(#sort_results_by#) asc
34 : </cfquery>
35 : <cfset registrant = registrant2>

SQL select * from registrant order by UPPER(FIRSTNAME) asc
Resources:

* Check the ColdFusion documentation to verify that you are using the correct syntax.
* Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1 (.NET CLR 3.5.30729)
Remote Address Removed this for my security
Referrer colin.cfm
Date/Time 15-Aug-09 01:46 PM
Stack Trace
at cfcolin2ecfm622248302.runPage(colin.cfm:33) at cfcolin2ecfm622248302.runPage(colin.cfm:33)

java.sql.SQLException: <br><b>Query Of Queries syntax error.</b><br>
Encountered "UPPER.
Incorrect ORDER BY column reference [UPPER].<br> Only simple column reference, alias name, and integer column id are allowed. <br><b>Example</b>: You can use alias to refer to a complex expression: <br><i>SELECT (a+b)/2 as x FROM T ORDER BY x</i>

at coldfusion.sql.imq.jdbcStatement.parseSQL(jdbcStatement.java:566)
at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:536)
at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131)
at coldfusion.sql.Executive.executeQuery(Executive.java:775)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:240)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:500)
at cfcolin2ecfm622248302.runPage(colin.cfm:33)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:219)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:51)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:69)
at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:115)
at coldfusion.CfmServlet.service(CfmServlet.java:107)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:541)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:204)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:318)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:426)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:264)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

[Old Pedant]

Well, this is an incredibly ASININE limitation that Cold Fusion is putting on the query.

You would *THINK* that CF would allow any query at all and let the DB engine worry about whether it's valid or not. All the other server side technologies do that: ASP, JSP, PHP, ASP.NET. You have to wonder who made that silly decision.

However, did you not notice that the message *GAVE* you the way to work around the problem????

Example: You can use alias to refer to a complex expression:
SELECT (a+b)/2 as x FROM T ORDER BY x


So:

select *, UPPER(name) AS UCName
from registrant
order by UCname

Though it's really almost never a good idea to do SELECT *, in any case. You really should only SELECT the fields you actually need.

[Old Pedant]

Incidentally, if you google for "Only simple column reference, alias name, and integer column id are allowed" you get the answer and the fix (same as I gave) in the second "hit". No, I didn't do the googling until after suggesting the fix. Was just curious if the CF people had any excuse for this idiocy. They didn't. Just presented the limitation with not comments on why.

[dreamingdigital]

Thank you so much. I'll try that. :thumbsup:

I agree that ColdFusion diverges from common sense on quite a few occasions!!

[bazz]

Have you thought about changing the inputted value from the user all to be lowercase and use that lowercase value for the match in the query?

Then if/when you output the value to a page/script/file, use CF or whatever to make the correct letters uppercase?

just a thought?

bazz

[dreamingdigital]

That's a good idea for data integrity.

[bazz]

There might be a way to make a search 'case insensitive'. Then it wouldn't matter whether they used UPPERCASE or lowercase in their search and your data in the db, could be correctly entered, capitalised.

hang on a minute, I have a search feature which iirc does exactly that :eek:

I'll look it up :)

.......

[bazz]

yeh, it uses LIKE and so, if the person enters a portion of their name, it would show all the possibilities case insensitvely.

Maybe not what you want because you could enter john s and be returned with everyone's name where they are called John S....?

And... I just read your OP again and my first idea is rubbish :(

You are trying to sort the db data so it doesn;t matter what the search values are - so long as they retrieve the correct data. The sort will be performed on the db data so look up the docs to see if there is a case insiensitive way to sort it. All my own data is capitalised so I don;t have to wory about that yet.

bazz

[bazz]

this might get you somewhere

http://search.mysql.com/search?site=refman-50&q=sort+case+insensitive&lr=lang_en

bazz