Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, My problem is "case sensitive" sql statement...
I would like to order these names, but the problem is, the names starts in Upper and in Lower case, so first it orders lower and then upper case.

How to IGNORE LOWER-UPPER CASE in sql statement? 
$SQLST="select name from table order by name";

[download]
Thanks a lot:)
Nejc

2004-12-15 Edited by Arunbear: Changed title from 'Sql statemet (how to ignore lower-upper case)', as per Monastery guidelines

Replies are listed 'Best First'.
Re: OT: Sql statemet (how to ignore lower-upper case)
by castaway (Parson) on Dec 15, 2004 at 11:26 UTC
    This has nothing to do with Perl, but.. 

    select name from table order by LCASE(name)

    [download]
    (assuming standard-ish SQL, it may be 'lower' and not 'lcase' - look in your documentation)

    C.

[reply]
[d/l]
Re: OT: Sql statement (how to ignore lower-upper case)
by pelagic (Priest) on Dec 15, 2004 at 11:56 UTC
    And this still has absolutely nothing to do with Perl but I just have to say it again:
    Watch out using functions like lower, upper or what not in WHERE clauses like:
    select name
from   table
where  lower(name) = 'fubar'

    [download]
    Using a function in a where clause on a database column prevents the database from using any indexes. It has to apply the function to all rows first before it can decide about the selection and that's disastrous for performance if it is a big table.

    pelagic
[reply]
[d/l]
      I realize that your statement is true for most database engines, but Postgres has a nifty feature.

      thor

      Feel the white light, the light within
      Be your own disciple, fan the sparks of will
      For all of us waiting, your kingdom will come

[reply]
        Interesting!
        Even Oracle got their Funcion Based Index. If you know in advance that you will be using something like that, it helps a lot!

        pelagic
[reply]
Re: OT: Sql statemet (how to ignore lower-upper case)
by dragonchild (Archbishop) on Dec 15, 2004 at 13:17 UTC
    The other option would be to sort or group the values in your Perl code instead of in SQL. Often, I do this because I need to user-defined sorting/grouping and it's easier to do that work in Perl than trying to dynamically generate SQL to do it, especially across multiple databases.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
Re: OT: Sql statemet (how to ignore lower-upper case)
by gellyfish (Monsignor) on Dec 15, 2004 at 13:55 UTC

    some RDBMS (such as MS SQL Server) allow you to specify a collation scheme on your database, tables or even columns within a table that will cause an ORDER BY to be done in a case-insensitive fashion by default. If you have such an RDBMS it is probably most efficient to use this facility.

    /J\

[reply]
[d/l]

Back to Seekers of Perl Wisdom