SQL Server ignores case in where clause

Question

How can I build a SQL query (MS SQL Server) where the where clause is case insensitive?

SELECT * FROM myTable WHERE myField = 'sOmeVal' 

I want the results to return, ignoring the case

Answer 1

In the standard SQL Server database configuration, row comparison case insensitive. If your database overrides this option (using alternative sorting), you need to specify what type of sorting to use in your query.

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

Please note that the mapping I provided is just an example (although it will be more than likely, just great for you). A more detailed description of SQL Server collisions can be found here .

Answer 2

Typically, string comparisons are case insensitive. If your database is case sensitive, you must force case insensitive case:

 SELECT balance FROM people WHERE email = '[e-mail]' COLLATE SQL_Latin1_General_CP1_CI_AS 

Answer 3

I found another solution elsewhere; i.e. use

 upper(@yourString) 

but everyone here says it doesn't matter in SQL Server because it ignores the case? I am sure our database is case sensitive.

Answer 4

No, just using LIKE will not work. LIKE searches for values ​​that match exactly this pattern. In this case, LIKE will only find the text 'sOmeVal', not 'someval'.

The decisive solution uses the LCASE() function. LCASE('sOmeVal') gets the line of your text: "someval". If you use this function for both sides of your comparison, it works:

SELECT * FROM myTable WHERE LCASE(myField) LIKE LCASE('sOmeVal')

The operator compares two line strings, so your 'sOmeVal' will match any other notation of 'someval' (for example, "Someval", "sOMEVAl", etc.).

Answer 5

You can force case sensitivity by choosing varbinary as:

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

Answer 6

The top 2 answers (from Adam Robinson and Andrei Kainikov ) are quite correct, as they technically work, but their explanations are incorrect and in many cases can be misleading. For example, although the SQL_Latin1_General_CP1_CI_AS will work in many cases, it should not be considered a suitable collation without the SQL_Latin1_General_CP1_CI_AS register. In fact, given that OP operates in a case-sensitive sorted database (or possibly binary), we know that OP does not use the sorting that is used by default for so many installations (especially any installed on the OS). using American English as the language): SQL_Latin1_General_CP1_CI_AS . Of course, the OP can use SQL_Latin1_General_CP1_CS_AS , but when working with VARCHAR data, it is important not to change the code page, as this can lead to data loss, and this is controlled by the language / culture of matching (i.e. Latin1_General vs French against Hebrew, etc. ) Please see item No. 9 below.

The remaining four answers are incorrect to varying degrees.

I will clear up all the misunderstandings so that readers can make the most appropriate / effective choice.

1. Do not use UPPER() . This is completely unnecessary extra work. Use the COLLATE . String comparisons need to be done anyway, but using UPPER() should also check, character by character, to see if there is a mapping in uppercase, and then change it. And you need to do this on both sides. Adding COLLATE simply directs the processing to generate sort keys using a different set of rules than the default one. Using COLLATE definitely more efficient (or "productive" if you like the word :) than using UPPER() , as proven in this test script (in PasteBin) .

   There is also a problem noted by @Ceisc in @Danny's answer:

   In some languages, conversions are done the wrong way. i.e. LOWER (x)! = LOWER (UPPER (x)).

   Turkish uppercase "İ" is a common example.

2. No, sorting is not an entire database setup, at least not in this context. There is a default mapping at the database level, and it is used as the default for modified and newly created columns that do not specify a COLLATE (which is probably due to this common misconception), but it does not directly affect queries. unless you are comparing string literals and variables with other string literals and variables, or referencing database level metadata.

3. No, matching is not on demand.

4. Mappings are made by predicate (that is, something operand) or expression, and not by request. And this is true for the whole query, and not just for WHERE . This includes CONNECTIONS, GROUP BY, ORDER BY, PARTITION BY, etc.

5. No, do not convert to VARBINARY (for example, convert(varbinary, myField) = convert(varbinary, 'sOmeVal') ) for the following reasons:

   1. this is a binary comparison that is not case sensitive (this is what this question asks)
   2. if you want binary comparison use binary matching. Use the one that ends with _BIN2 if you are using SQL Server 2008 or later, otherwise you have no choice but to use the one that ends with _BIN . If the data is NVARCHAR then it does not matter which locale you use, since they are all the same in this case, therefore Latin1_General_100_BIN2 always works. If the data is VARCHAR , you must use the same locale in which the data is located (e.g. Latin1_General , French , Japanese_XJIS , etc.), because the locale defines the codepage used, and changing the code pages can change the data (i.e. data loss).
   3. using a variable length data type without specifying a size will depend on the default size, and there are two different default values ​​depending on the context in which the data type is used. This is either 1 or 30 for string types. When used with CONVERT() it will use the default value of 30. The danger is that if the string length can exceed 30 bytes, it will be silently truncated, and you will probably get incorrect results from this predicate.
   4. Even if you want case-sensitive comparisons, binary comparisons are not case-sensitive (another very common misconception).

6. No, LIKE not always case sensitive. It uses the collation of the referenced column, or the collation of the database if the variable is compared to a string literal, or the collation specified in the optional COLLATE .

7. LCASE not a SQL Server feature. It looks like it's either Oracle or MySQL. Or perhaps Visual Basic?

8. Since the context of the question compares a column with a string literal, neither instance matching (often called a "server") nor database matching has any direct effect here. Sorting options are stored for each column, and each column may have different sorting options, and these sorting options do not have to match the default database sorting options or instance sorting options. Of course, instance matching is the default for what the newly created database will use as the default matching if the COLLATE was not specified when creating the database. Similarly, the default collation for the database is what the modified or just created column will use if the COLLATE was not specified.

9. Case-insensitive matching should be used, which otherwise matches the column mapping. Use the following query to find the column sort options (change the table name and schema name):

    SELECT col.* FROM sys.columns col WHERE col.[object_id] = OBJECT_ID(N'dbo.TableName') AND col.[collation_name] IS NOT NULL; 

   Then just change _CS to _CI . Thus, Latin1_General_100_CS_AS will become Latin1_General_100_CI_AS .

   If the column uses a binary mapping (ending in _BIN or _BIN2 ), find a similar mapping using the following query:

    SELECT * FROM sys.fn_helpcollations() col WHERE col.[name] LIKE N'{CurrentCollationMinus"_BIN"}[_]CI[_]%'; 

   For example, assuming the column uses Japanese_XJIS_100_BIN2 , do this:

    SELECT * FROM sys.fn_helpcollations() col WHERE col.[name] LIKE N'Japanese_XJIS_100[_]CI[_]%'; 

For more information about collation, encoding, etc., please visit: Collations information

Answer 7

What database are you working in? With MS SQL Server, this is a database parameter, or you can overload it for each query using the COLLATE keyword.