0

I am working on a query so that I can compare a word read from a file name to another word that is already predefined in the code and assign it a numeric value.

However, the issue I am running into is trying to ignore the case of the name, because the name is being read from a file the case could change and then would not match the predefined value I have set. Is there any easy way to ignore the capitalization other than putting every possible value in the case?

code snipit:

NumberVal = CASE   
              WHEN Name = 'Bond' OR Name = 'BOND' OR Name = 'bond' THEN 16   
            END
buzzzzjay
  • 1,140
  • 6
  • 27
  • 54
  • 2
    Isnt SQL-Server Case insensitive by default? Have you modified that setting? – Matt Jul 15 '11 at 15:32
  • 1
    @Yoda, SQL Server is case sensative depending on the collation set on the data. – DForck42 Jul 15 '11 at 20:19
  • No, please don't use `UPPER` for this. That's definitely less efficient, and sometimes more error prone, than the proper method of specifying an explicit collation via `COLLATE`. Find the column's collation and use the case-insensitive version of it. Just change the `_CS` to `_CI` in the collation name. So `Latin1_General_100_CS_AS` becomes `Latin1_General_100_CI_AS`, and this predicate becomes `WHEN [Name] COLLATE Latin1_General_100_CI_AS = 'Bond' THEN 16`. Please see [my answer to this related question](https://stackoverflow.com/a/54431309/577765) for details, especially points 1 and 9. – Solomon Rutzky Feb 04 '19 at 22:36
  • @Matt Technically no, SQL Server can be installed with any collation you choose. And there are several levels which control different things and can have different collations. But on a practical level, the default collation for the instance on US English OS's is `SQL_Latin1_General_CP1_CI_AS`, which is case-insensitive. The instance's collation controls instance-level objects such as Logins, is the collation of `tempdb`, and is the default for new DBs that don't specify a collation. Then, the DB's collation is the default for new columns that don't specify a collation. https://collations.info/ – Solomon Rutzky Feb 04 '19 at 23:37

1 Answers1

5

SQL Server be default ignores case so someone has changed the setting ot youdidn't know

If it is now case sensitive, just use UPPER and forget collation

CASE   
  WHEN UPPER(Name) = 'BOND' THEN 16   
 END
gbn
  • 422,506
  • 82
  • 585
  • 676
  • No, please don't use `UPPER` for this. That's definitely less efficient, and sometimes more error prone, than the proper method of specifying an explicit collation via `COLLATE`. Find the column's collation and use the case-insensitive version of it. Just change the `_CS` to `_CI` in the collation name. So `Latin1_General_100_CS_AS` becomes `Latin1_General_100_CI_AS`, and this predicate becomes `WHEN [Name] COLLATE Latin1_General_100_CI_AS = 'Bond' THEN 16`. Please see [my answer to this related question](https://stackoverflow.com/a/54431309/577765) for details, especially points 1 and 9. – Solomon Rutzky Feb 04 '19 at 22:34