"Order By" Sorts all capitals before any lower-case letters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gregl@gregl.net - 30 Mar 2006 21:38 GMT

I have a large CSV table that I'm accessing through ADO and the ODBC
text driver.

The ODBC text driver won't allow using ADO's Sort command, so I'm
sorting in the SQL Statement with an "Order By" clause. But, it sorts
all capital letters before any lower-case letters, so that "Z" comes
before "a".

Any suggestions on what I can do to get a case-sensitive sort?

Thanks,

Greg

Reply to this Message

Richard Mueller - 30 Mar 2006 22:00 GMT

Greg wrote:

>I have a large CSV table that I'm accessing through ADO and the ODBC
> text driver.
[quoted text clipped - 5 lines]
>
> Any suggestions on what I can do to get a case-sensitive sort?

Hi,

I would use the Upper or Lower functions. Perhaps:

ORDER BY LOWER(Field_Name)

Signature

Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

Reply to this Message

gregl@gregl.net - 31 Mar 2006 00:45 GMT

Thanks, but it looks like the odbc text driver doesn't allow that,
either:

?err.Description
[Microsoft][ODBC Text Driver] Undefined function 'lower' in expression.

Same problem with upper.

It looks like the people who create the CSV, a Great Plains provider,
will be able to save out the CSV already sorted.

Thanks,

Greg

Reply to this Message

Richard Mueller - 31 Mar 2006 02:18 GMT

Strange. The "Order By" clause is SQL. Transact-SQL includes the Lower and
Upper functions. My SQL reference says these functions can be used in "Order
By" clauses ("Visual Quickstart Guide to SQL" by Chris Fehily, PeachPit
Press). ODBC simply passes the SQL statements to the database. This almost
looks like your database DBMS doesn't support this.

If Lower cannot be used in an "Order By" clause, how about in the "Select"
clause. Perhaps

SELECT LOWER(Field_Name) As LowerField, ..... ORDER BY LowerField

where we create an alias (LowerField in my example) for the field we want to
sort on. Just a thought.

Signature

Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

> Thanks, but it looks like the odbc text driver doesn't allow that,
> either:
[quoted text clipped - 10 lines]
>
> Greg

Reply to this Message

Paul Clement - 31 Mar 2006 15:20 GMT

¤ Thanks, but it looks like the odbc text driver doesn't allow that,
¤ either:
¤
¤ ?err.Description
¤ [Microsoft][ODBC Text Driver] Undefined function 'lower' in expression.
¤
¤ Same problem with upper.
¤
¤ It looks like the people who create the CSV, a Great Plains provider,
¤ will be able to save out the CSV already sorted.
¤

Try LCase instead of Lower. If it doesn't work with the ODBC driver it should work with Jet OLEDB
and the Text ISAM driver.

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

VB Forum / Database Access / March 2006

"Order By" Sorts all capitals before any lower-case letters