'How can I write a case sensitive WHERE CLAUSE in SQL Server?

Ok, this one has me stumped. I'm hoping it's not something stupid/obvious...

I'm trying to fix addresses in a client database. I have a function that takes a string and returns a string where the first letter of every word is capitalized.

I am trying to only pass the addresses that are all caps, or all lowercase, but it's returning the entire dataset instead. The first three columns are all correct, so I can't figure out why the WHERE isn't working.

Here's my code.

    SELECT
        [ConvertFirstLettertoCapital]([Address]) AS [Standard],
        UPPER([Address]) AS [Upper],
        LOWER([Address]) AS [Lower],
        [Name],
        [Address],
        [Address 2],
        [City],
        [State]
    FROM [Address_List]
    WHERE
        [Address] = UPPER([Address]) OR
        [Address] = LOWER([Address])
sqlsql-serverwhere-clausecase-sensitivesql-server-2019


Solution 1:[1]

Normally SQL Server is not case sensitive. So 'ABC'='abc' is true in a where clause.

To make a where clause case sensitive, you can use COLLATE. Please try the below query instead:

WHERE
        [Address] = UPPER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
        OR
        [Address] = LOWER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Peter Mortensen

Related Questions