5

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])
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Dizzy49
  • 1,360
  • 24
  • 35
  • 2
    What collation is your database / table set to? Your WHERE clause will match the entire column if your collation is one of the non-case-sensitive collations which is frequently the case in MSSQL. – squillman Feb 04 '21 at 19:05
  • 3
    If the database is in a case insensitive (and accent insensitive) collation, then it'll return every row where `Address` has a not `NULL` value. *Normally* databases are case insensitive, and only use a case sensitive when you really need it; seeing you don't state it is in one I doubt it is. Thus `'my àddrêss'` = `'MY ADDRESS'`. – Thom A Feb 04 '21 at 19:06
  • 1
    @squillman It's 'COLLATE SQL_Latin1_General_CP1_CI_AS' I am guessing that CI = Case Insensitive – Dizzy49 Feb 04 '21 at 19:41
  • 1
    That is correct, CI = Case Insensitive. https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15 – squillman Feb 04 '21 at 20:26

1 Answers1

8

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
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26