Why does the data explorer not support ILIKE, SIMILAR TO, or regular expressions ~?

Talking to the devs:
Would it be possible to add that functionality?

  • The whole Stack Exchange platform is based on Windows and MSSQL; those functions do not exist in MSSQL. Still, as SEDE does not directly execute queries, it should be possible to 'emulate' that functionality. – AStopher Aug 24 '15 at 20:48

Data Explorer isn't backed by PostgreSQL, but a SQL Server instance, and those functions aren't defined in T-SQL. While it would be possible to allow regex via CLR functions, that isn't something that will be added.

ILIKE should be generally easily emulated using LOWER(column) LIKE … though, where the acceptable pattern characters can be found in the LIKE documentation.

  • ok, thank you for responding. PS I really liked your moderator stats page for the elections... and your avatar. :) – David Aug 24 '15 at 20:48
  • 4
    Instead of Lower you can use collate as well: where title like '%Function%' collate Latin1_General_100_ci_ai as shown here – rene Aug 24 '15 at 21:00

Outside of the aforementioned LIKE clause, PATINDEX is the only pattern matching function. The following are supported by both PATINDEX and LIKE:

Wildcard Specification      Description
%                           Matches zero of more characters
_                           Matches any single character
[]                          Matches a range [A-Z] or a set [!@#$%^&*(){}|]
[^]                         Excludes a range or a set as noted above


  • That's not true a standard LIKE supports limited pattern matching in SQL Server: stackoverflow.com/q/8928378/458741 – ben is uǝq backwards Sep 9 '15 at 21:34
  • @benisuǝqbackwards The only pattern matching function is PATINDEX. LIKE is not a function, so the statement is true. – Paul Sweatte Sep 9 '15 at 22:54
  • 2
    @PaulSweatte: Your assertion is true, but the implication is misleading, especially given that the assertion narrows the scope of the question without clearly excluding the rest of it. – Nathan Tuggy Sep 9 '15 at 23:53
  • @NathanTuggy Sorry. I was just covering what was not mentioned in the previous answer. Is that narrowing the scope of the question, given that the existing answer refers to LIKE? – Paul Sweatte Sep 10 '15 at 14:26
  • @PaulSweatte: The question doesn't really limit itself to functions. It's concerned only with getting some sort of built-in support for pattern matching; whether that be syntactically a "function" or something else is beside the point. – Nathan Tuggy Sep 10 '15 at 16:29
  • @NathanTuggy Thanks for the clarification. I've updated the answer with context. – Paul Sweatte Sep 11 '15 at 16:50

You must log in to answer this question.

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