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

Morpheus

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??

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

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