T-SQL find string with lowercase and uppercase

Question

I have a database with several tables and I need to search every varchar column across the database, for columns that simultaneously contain lower and upper case characters.

To clarify:

If one column contains helLo the name of the column should be returned by the query, but if the column values only contain either hello or HELLO then the name of the column is not returned.

Solution 1

Let's exclude all UPPER and all LOWER, the rest will be MIXED.

SELECT someColumn
FROM someTable
WHERE someColumn <> UPPER(someColumn) AND someColumn <> LOWER(someColumn) 

EDIT:

As suggested in comments and described in detail here I need to specify a case-sensitive collation.

SELECT someColumn
FROM someTable
WHERE someColumn <> UPPER(someColumn) AND 
      someColumn <> LOWER(someColumn) 
      Collate SQL_Latin1_General_CP1_CS_AS

Solution 2

It sounds like you are after a case sensitive search, so you'd need to use a case sensitive collation for there WHERE clause.

e.g. if your collation is currently SQL_Latin1_General_CP1_CI_AS which is case insensitive, you can write a case sensitive query using:

SELECT SomeColumn
FROM dbo.SomeTable
WHERE SomeField LIKE '%helLo%' COLLATE SQL_Latin1_General_CP1_CS_AS

Here, COLLATE SQL_Latin1_General_CP1_CS_AS tells it to use a case sensitive collation to perform the filtering.