9

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?

1
  • 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, 2015 at 20:48

2 Answers 2

15

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.

2
  • ok, thank you for responding. PS I really liked your moderator stats page for the elections... and your avatar. :)
    – David
    Aug 24, 2015 at 20:48
  • 5
    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, 2015 at 21:00
6

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

References

6
  • That's not true a standard LIKE supports limited pattern matching in SQL Server: stackoverflow.com/q/8928378/458741 Sep 9, 2015 at 21:34
  • @benisuǝqbackwards The only pattern matching function is PATINDEX. LIKE is not a function, so the statement is true. Sep 9, 2015 at 22:54
  • 3
    @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. Sep 9, 2015 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? Sep 10, 2015 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. Sep 10, 2015 at 16:29
  • @NathanTuggy Thanks for the clarification. I've updated the answer with context. Sep 11, 2015 at 16:50

You must log in to answer this question.

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