SQL server ignore case in a where expression

sql sql-server where-clause case-insensitive
Raul Agrait picture Raul Agrait · Aug 3, 2009 · Viewed 146.3k times · Source

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

Adam Robinson picture Adam Robinson · Aug 3, 2009

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.

Related questions

SQL Server : check if variable is Empty or NULL for WHERE clause

When searching for a list of products, the @SearchType parameter is optional. If @SearchType is empty or NULL then it should return all products and not use the WHERE clause. Otherwise, if it passed Equipment it would then use that …

Read More
Conditional WHERE clause in SQL Server

I am creating a SQL query in which I need a conditional where clause. It should be something like this: SELECT DateAppr, TimeAppr, TAT, LaserLTR, Permit, LtrPrinter, JobName, JobNumber, JobDesc, ActQty, (ActQty-LtrPrinted) AS L, (ActQty-QtyInserted) AS M, ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) …

Read More
SQL - improve NOT EXISTS query performance

Is there a way I can improve this kind of SQL query performance: INSERT INTO ... WHERE NOT EXISTS(Validation...) The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT …

Read More