Add STARTS_WITH and ENDS_WITH

[saschahofmann]

In ibis-project/ibis-bigquery#61 , I suggested to add the big query functions STARTS_WITH and ENDS_WITH to the BigQuery repo. @tswast, rightfully, pointed out that this could go into the main repo.

Should I go ahead and make a PR? Any thing I should look out for?

I understand the operation definitions belong in operations.py. Would a PR also require an implementation for each backend?

My current implementation looks for BigQuery looks like this:

import ibis.expr.rules as rlz
from ibis.expr.operations import Arg, ValueOp
from ibis.expr.types import StringValue
from ibis_bigquery import BigQueryExprTranslator

compiles = BigQueryExprTranslator.compiles


class StartsWith(ValueOp):
    value = Arg(rlz.string)
    start_string = Arg(rlz.string)
    output_type = rlz.shape_like("value", "bool")


def startswith(value, start_string):
    return StartsWith(value, start_string).to_expr()


class EndsWith(ValueOp):
    value = Arg(rlz.string)
    end_string = Arg(rlz.string)
    output_type = rlz.shape_like("value", "bool")


def endswith(value, start_string):
    return EndsWith(value, start_string).to_expr()


StringValue.startswith = startswith
StringValue.endswith = endswith


@compiles(StartsWith)
def _startswith(t, expr):
    # pull out the arguments to the expression
    value, start_string = expr.op().args
    # compile the argument
    t_value = t.translate(value)
    t_start = t.translate(start_string)
    # return a SQL expression that calls the BigQuery STARTS_WITH function
    return f"STARTS_WITH({t_value}, {t_start})"


@compiles(EndsWith)
def _endswith(t, expr):
    # pull out the arguments to the expression
    value, start_string = expr.op().args
    # compile the argument
    t_value = t.translate(value)
    t_start = t.translate(start_string)
    # return a SQL expression that calls the BigQuery STARTS_WITH function
    return f"ENDS_WITH({t_value}, {t_start})"

[datapythonista]

I don't know any database that has specific functions for those. In SQL this is implemented as LIKE 'value%'. Could be implemented in Ibis, but not sure if any other backend will benefit from it. @jreback thoughts?

[saschahofmann]

Ah yeah, I thought I checked whether Postgres does it last week but now I can't find it so apparently I didn't 😅 .

SQLAlchemy implements it as well. He does the LIKE 'value%' approach.

[saschahofmann]

And maybe there could be an argument made for pandas having it as well?

[saschahofmann]

Anyway, happy to implement it for bigquery specifically if that's the consens.

[datapythonista]

If SQLAlchemy implements it, happy to take a PR for it. Should be easy to add to all backends.

[saschahofmann]

Is there a way to add the SQLAlchemy implementation? Or is each backend handling it individually?

[datapythonista]

We have a base SQL backend in backends/base/sql and a base SQLAlchemy backend in backends/base/sql/alchemy. Adding the translation functions to each of them should make them work in all backends.