How to find values in all caps in SQL Server?

Question

How can I find column values that are in all caps? Like LastName = 'SMITH' instead of 'Smith'

Here is what I was trying...

SELECT *
  FROM MyTable
 WHERE FirstName = UPPER(FirstName)

Answer 1

You can force case sensitive collation;

select * from T
  where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS

Answer 2

Try

 SELECT *
  FROM MyTable
 WHERE FirstName = UPPER(FirstName) COLLATE SQL_Latin1_General_CP1_CS_AS

This collation allows case sensitive comparisons.

If you want to change the collation of your database so you don't need to specifiy a case-sensitive collation in your queries you need to do the following (from MSDN):

1) Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

2) Export all your data using a tool such as the bcp Utility.

3) Drop all the user databases.

4) Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
/SQLCOLLATION=CollationName

5) Create all the databases and all the objects in them.

6) Import all your data.

Answer 3

You need to use a server collation which is case sensitive like so:

SELECT * 
FROM MyTable
WHERE FirstName = UPPER(FirstName) Collate SQL_Latin1_General_CP1_CS_AS

Answer 4

Try

SELECT *
  FROM MyTable
 WHERE FirstName = LOWER(FirstName)

Answer 5

Be default, SQL comparisons are case-insensitive.

Answer 6

You can find good example in Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server

Answer 7

I created a simple UDF for that:

create function dbo.fnIsStringAllUppercase(@input nvarchar(max)) returns bit

    as

begin

    if (ISNUMERIC(@input) = 0 AND RTRIM(LTRIM(@input)) > '' AND @input = UPPER(@input COLLATE Latin1_General_CS_AS))
        return 1;

    return 0;
end

Then you can easily use it on any column in the WHERE clause.

To use the OP example:

SELECT *
FROM   MyTable
WHERE  dbo.fnIsStringAllUppercase(FirstName) = 1

Answer 8

Simple way to answer this question is to use collation. Let me try to explain:

SELECT *
  FROM MyTable
 WHERE FirstName COLLATE SQL_Latin1_General_CP1_CI_AS='SMITH’

In the above query I have used collate and didn’t use any in built sql functions like ‘UPPER’. Reason because using inbuilt functions has it’s own impact.

Please find the link to understand better:

performance impact of upper and collate

Answer 9

Could you try using this as your where clause?

WHERE PATINDEX(FirstName + '%',UPPER(FirstName)) = 1

Answer 10

Have a look here

Seems you have a few options

• cast the string to VARBINARY(length)

• use COLLATE to specify a case-sensitive collation

• calculate the BINARY_CHECKSUM() of the strings to compare

• change the table column’s COLLATION property

• use computed columns (implicit calculation of VARBINARY)

Answer 11

Try This

SELECT *
FROM MyTable
WHERE UPPER(FirstName) COLLATE Latin1_General_CS_AS = FirstName COLLATE Latin1_General_CS_AS