How to use LIKE / RLIKE operators in Transformations on Data Modeling [Cognite Official]

LIKE and its shortcomings

When using wildcards with the LIKE operator in a transformation query, behavior in the Data Modeling can vary depending on the position of the wildcard.

Wildcards placed at the end or in the middle of a string are supported, whereas those placed at the beginning or on both sides may not work as expected.

The following examples illustrate this behavior:

select 

  externalId    

  from cdf_data_models("mips_sourcemodel","mips_sourcemodel","1", "Mips_object")

where object_name like "%name"

-- Error: Unsupported filter 'StringEndsWith', StringEndsWith(object_name,name)

select 

  externalId    

  from cdf_data_models("mips_sourcemodel","mips_sourcemodel","1", "Mips_object")

where object_name like "%name%"

-- Error: Unsupported filter 'StringContains', StringContains(object_name,name)

This behavior occurs because LIKE queries are translated into CDF API filters wherever possible. This translation only supports prefix filters, meaning any other pattern matching must be handled by Spark itself.

What RLIKE brings to the table

To work around this limitation, you can use the RLIKE operator with Data Modeling.

If you need to retrieve all results that match a given expression, use RLIKE instead of LIKE. See the example below:

select

 cast(`name` as STRING) as key

from

 cdf_data_models("shashanDataModel", "Shashan_MovieDM", "1", "Movie")

 where name RLIKE 'cl';

The RLIKE operator is case-sensitive by default. To perform a case-insensitive match, you can add (?i) to your expression. See the example below:

select

 cast(`name` as STRING) as key

from

 cdf_data_models("shashanDataModel", "Shashan_MovieDM", "1", "Movie")

 where name RLIKE '(?i).*ma.*';

Have Questions?

Ask them in the comments below or post in our Product User Community. Our team and fellow learners are here to help!

Need more details?
For more information on RLIKE read the following documentation and tutorials.

https://www.geeksforgeeks.org/sql/rlike-operator-in-mysql/

https://docs.databricks.com/aws/en/sql/language-manual/operators/rlike