Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 153 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

Why is the like operator case sensitive in data explorer? Which database is being used on the backend? Sql Server's like operator was case insensitive the last time I checked.

share|improve this question
up vote 5 down vote accepted

It depends on the collation of the column, which is normally the database's default collation, unless it was explicitly set. You can change it in a query with the COLLATE clause, as shown in this SEDE query.

I have also provided a query to retrieve the available collations.

share|improve this answer
1  
Mark, just FYI, the "::" syntax has been deprecated for a while, I think as of SQL Server 2005. The preferred syntax to get the collation info is sys.fn_helpcollations(). – srutzky Oct 4 '15 at 20:24
1  
Regarding "it just depends on the default collation in use": there are actually 3 sources for determining the collation of a predicate: operations involving only literals and variables use the database default (if not overridden via the COLLATE keyword), the definition of the field (which doesn't have to match the database default), and the COLLATE keyword. Please see my answers SQL Server default character encoding and What is the point of COLLATIONS for nvarchar (Unicode) columns? for more details :). – srutzky Oct 4 '15 at 20:31
1  
Essentially, in the stated case of using a LIKE statement against a field, the "default collation in use" is not the database's default collation but the field's collation, which could be anything. But in practical terms, the collation of a field typically mirrors that of the database default since that is the collation chosen for a field if the COLLATE clause is not specified in the CREATE TABLE query or a subsequent ALTER TABLE ... ALTER COLUMN query. – srutzky Oct 4 '15 at 20:39
    
@srutzky I've updated the SEDE query to use sys.. Considering changes to my answer now. – Mark Hurd Oct 5 '15 at 1:29

Its like @MarkHurd said, it depends on the collation of the database, if its case sensitive, then comparisons like = or LIKE are gonna be case sensitive. You can do a query and force a collation that is case insensitive. Something like this:

SELECT TOP 10 *
FROM Posts
WHERE Body LIKE '%something%' COLLATE Modern_Spanish_CI_AS
share|improve this answer
1  
Regarding "it depends on the collation of the database": there are actually 3 sources for determining the collation of a predicate: operations involving only literals and variables use the database default (if not overridden via the COLLATE keyword), the definition of the field (which doesn't have to match the database default), and the COLLATE keyword. Please see my answers SQL Server default character encoding and What is the point of COLLATIONS for nvarchar (Unicode) columns? for more details :). – srutzky Oct 4 '15 at 20:32
1  
Essentially, in the stated case of using a LIKE statement against a field, it is not the database's default collation being used, but the field's collation, which could be anything. But in practical terms, the collation of a field typically mirrors that of the database default since that is the collation chosen for a field if the COLLATE clause is not specified in the CREATE TABLE query or a subsequent ALTER TABLE ... ALTER COLUMN query. – srutzky Oct 4 '15 at 20:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .