How to find values in all caps in SQL Server?

问题:

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)

回答1:

You can force case sensitive collation;

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

回答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.

回答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

回答4:

Be default, SQL comparisons are case-insensitive.

回答5:

Could you try using this as your where clause?

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

回答6:

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)

回答7:

Try This

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

回答8:

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

回答9:

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

回答10:

Try

SELECT *
  FROM MyTable
 WHERE FirstName = LOWER(FirstName)