CODEWITHSUNDEEP
sqlsql-serveruppercase
daveomcd
daveomcd

Reputation: 6555

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)

Upvotes: 41

Views: 65882

Answers (11)

KamalDeep
KamalDeep

Reputation: 834

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

Upvotes: 0

Despedo
Despedo

Reputation: 39

Try

SELECT *
  FROM MyTable
 WHERE FirstName = LOWER(FirstName)

Upvotes: 1

Anja
Anja

Reputation: 324

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

Upvotes: 3

isapir
isapir

Reputation: 23513

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

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

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

Upvotes: 0

Sequenzia
Sequenzia

Reputation: 2381

Try This

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

Upvotes: 0

Joe Ratzer
Joe Ratzer

Reputation: 18549

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.

Upvotes: 8

Richard Friend
Richard Friend

Reputation: 16018

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)

Upvotes: 0

Alex K.
Alex K.

Reputation: 175816

You can force case sensitive collation;

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

Upvotes: 66

Nonym
Nonym

Reputation: 6299

Could you try using this as your where clause?

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

Upvotes: 0

drdwilcox
drdwilcox

Reputation: 3951

Be default, SQL comparisons are case-insensitive.

Upvotes: 1

Related Questions