SQL server ignore case in a where expression

Question

How do I construct a SQL query (MS SQL Server) where the "where" clause is case-insensitive?

SELECT * FROM myTable WHERE myField = 'sOmeVal'

I want the results to come back ignoring the case

Answer 1

What database are you on? With MS SQL Server, it's a database-wide setting, or you can over-ride it per-query with the COLLATE keyword.

Answer 2

In the default configuration of a SQL Server database, string comparisons are case-insensitive. If your database overrides this setting (through the use of an alternate collation), then you'll need to specify what sort of collation to use in your query.

SELECT * FROM myTable WHERE myField = 'sOmeVal' COLLATE SQL_Latin1_General_CP1_CI_AS

Note that the collation I provided is just an example (though it will more than likely function just fine for you). A more thorough outline of SQL Server collations can be found here.

Answer 3

Usually, string comparisons are case-insensitive. If your database is configured to case sensitive collation, you need to force to use a case insensitive one:

SELECT balance FROM people WHERE email = '[email protected]'
  COLLATE SQL_Latin1_General_CP1_CI_AS

Answer 4

If the Database is case sensitive you can't. To create a case insensitive db choose another collation on db creation. For the right collation see here.

Answer 5

You can force the case sensitive, casting to a varbinary like that:

SELECT * FROM myTable 
WHERE convert(varbinary, myField) = convert(varbinary, 'sOmeVal')

Answer 6

SELECT * FROM myTable WHERE myField LIKE 'sOmeVal' COLLATE SQL_Latin1_General_CP1_CI_AS

is not working for my H2 database. Can't I use the collation method for 'like' instead of '=' ?

Answer 7

I found another solution elsewhere; that is, to use

upper(@yourString)

but everyone here is saying that, in SQL Server, it doesn't matter because it's ignoring case anyway? I'm pretty sure our database is case-sensitive.