6

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.

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
4
  • 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().
    – Solomon Rutzky
    Commented Oct 4, 2015 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 :).
    – Solomon Rutzky
    Commented Oct 4, 2015 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.
    – Solomon Rutzky
    Commented Oct 4, 2015 at 20:39
  • @srutzky I've updated the SEDE query to use sys.. Considering changes to my answer now.
    – Mark Hurd
    Commented Oct 5, 2015 at 1:29
3

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
Improve this answer
2
  • 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 :).
    – Solomon Rutzky
    Commented Oct 4, 2015 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.
    – Solomon Rutzky
    Commented Oct 4, 2015 at 20:38

You must log in to answer this question.

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