Search code examples
prestotrino

How to use like operator in Presto with string that contain a dot?

I have a column string like this

"test.123.test" "something"

And I want run a query to find string like "test.*.test". In postgresql I use this query:

select * from table where string_column like 'test.%.test'

I run this query in presto but got nothing! It should be related to dot in my string because when I replace string like with something like this 'test.1%1.test' it work but it's not my result.

Solution

  • For a Presto query

    string_column like 'test.%.test'

    the predicate pushed down into the PostgreSQL connector is similar to:

    string_column BETWEEN 'test.' AND 'test/'

    however, string comparison are subject to collation and trailing punctuations hits an edge case of Presto/PostgreSQL incompatibility: https://github.com/trinodb/trino/issues/3645

    You can workaround this by preventing predicate pushdown into the connector. You can achieve this by adding OR rand() = 42 to your query:

    string_column like 'test.%.test' OR rand() = 42