Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL: how to tell the difference between upper and lower case?

Options
  • 19-02-2004 12:21pm
    #1
    Morpheus
    Moderators, Society & Culture Moderators Posts: 2,688 Mod ✭✭✭✭


    Anyway to tell the difference BETWEEN uppercase and lowercase in SQL???

    say youve two entries in a table that you wish to treat as different words?

    "hello world"
    and
    "HELLO WORLD"

    how would i construct a select statement that takes only the lowercase words??


Comments

  • Options
    #2
    Karoma
    Registered Users Posts: 19,396 ✭✭✭✭Karoma


    what server are you using?

    use built-in functions.. for Server:
    SQL server: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm


  • Options
    #3
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    Depends on what database you are writing the SQL for.

    Oracle and DB2, afaik, are case-sensitive by default.

    MSSQL, on the other hand, has a case-insensitive configuration by default. This can be changed to case-sensitive (by changing the sort order to be case sensitive) but this has the knock-on effect of requiring that all queries have table and field names in the correct case as well.

    If you *are* using MSSQL, then its a pretty tough one to solve without reconfiguring the server as mentioned above. Best suggestion I would have is to use the ASCII() function to check the *first* character of the string, but that doesn't ensure you'll always get the right answer.

    If its some other DB....you'd need to check teh available string functions to see if there's anything better.

    Ultimately, I always found that case-sensitivity in MSSQL was to hard to implement, so I did it on the client-side, or I just used the UPPER and LOWER functions to force everything into whatever case I wanted.

    As an afterthought...MSSQL 2000 supports user-defined functions. You could fairly easily write an IS_LOWER function which used a loop, the ASCII and SUNSTRING functions to check every character in a string and return True, False, or NULL appropriately. I wouldn't want to run it over a large amount of data though :)

    jc


Advertisement