Test for Upper Case - T-Sql

Question

All,

How can I check if a specified varchar character or entire string is upper case in T-Sql? Ideally I'd like to write a function to test if a character is upper case, then I can later apply that to a generic varchar. It should return false for non alphabetic characters. I am only interested in english language characters.

I am working with T-sql in SQL Management Studio, and I have tried pulling records beginning with a lower case letter from a table in this fashion:

select * from TABLE where SUBSTRING(author,1,1) != LOWER(SUBSTRING(author,1,1)) 

Which returns 0 records, but I know there are records beginning with upper and lower case letters.

Thanks

---

EDIT: Since both podiluska and joachim-isaksoon have successfully answered my question (Both methods work for my purposes), would someone mind explaining which would be the most efficient method to use to query a table with a large number of records to filter out records with authors beginning with or without a capital letter?

Answer 1

Using collations

eg:

if ('a'='A' Collate Latin1_General_CI_AI)      print'same 1' else     print 'different 1'  if ('a'='A' Collate Latin1_General_CS_AI)      print'same 2' else     print 'different 2'  

The CS in the collation name indicates Case Sensitive (and CI, Case Insensitive). The AI/AS relates to accent sensitivity.

or in your example

SUBSTRING(author,1,1) <> LOWER(SUBSTRING(author,1,1)) COLLATE Latin1_General_CS_AI