yes, that's right. Currently, Drill does not recognize the ESCAPE character, and will throw error. This also applies to "similar to" operator.
Please file a JIRA. We'll provide a fix to support ESCAPE for like/similar to operator. Thanks. Error: Missing function implementation: [like(VARCHAR-OPTIONAL, VARCHAR-REQUIRED, VARCHAR-REQUIRED)]. Full expression: --UNKNOWN EXPRESSION--.. [5d50a250-f7a7-492f-92fe-e408709432b6] " On Wed, Oct 15, 2014 at 6:04 PM, Norris Lee <[email protected]> wrote: > Thanks for the reply. I just tested this out in SQLLine, looks like this > is not supported in Drill. > > Does anyone else know? > > Norris > > -----Original Message----- > From: Julian Hyde [mailto:[email protected]] > Sent: Wednesday, October 15, 2014 2:45 PM > To: [email protected] > Subject: Re: LIKE clause escape character > > I believe that according to the SQL standard you have to provide one > explicitly. And therefore you get to choose your own. Most people use > back-slash. > > Thus: > > > Select * from INFORMATION_SCHEMA.`TABLES` where TABLE_NAME LIKE > 'bit#_table' ESCAPE '#' > > > will find only tables with underscores. The same character can escape both > '%' and '_'. > > I don't know whether Drill does this, but Calcite does. > > Julian > > > On Oct 14, 2014, at 7:41 PM, Norris Lee <[email protected]> wrote: > > > Hi, > > > > Does anyone know what the escape character is for the LIKE clause? > > Eg. Select * from INFORMATION_SCHEMA.`TABLES` where TABLE_NAME LIKE > > 'bit_table'; I want to look for the underscore, not a wildcard. > > > > Thanks, > > Norris > >
