topic Re: Incorta Regex formula in Data & Schema Discussions

Incorta Regex formula

mkrieger — Fri, 20 Oct 2023 17:22:42 GMT

Hi,

I am trying to get regex to work in a materialized view but keep running into "undefined function" errors

Is there something wrong with my format?

SELECT Name,

UniversalId,

Item_Description,

REGEXP_LIKE(Item_Description, '\d+') AS regex_column

FROM table

Re: Incorta Regex formula

JoeM — Fri, 20 Oct 2023 17:25:21 GMT

@mkrieger - This will depend on whether you are using SparkSQL or Postgres. Here are samples of how you might regex match in each example:

Postgres

SELECT CategoryName, (CategoryName ~ 'c') AS regex_column FROM OnlineStore.ProductCategory

Spark SQL

SELECT `OnlineStore.productcategory`.`CategoryName` AS `CategoryName`, regexp(`OnlineStore.productcategory`.`CategoryName`, 'c') AS regex_column FROM `OnlineStore.productcategory` `OnlineStore.productcategory`

Re: Incorta Regex formula

mkrieger — Fri, 20 Oct 2023 20:52:47 GMT

Thanks for the quick response!
I've confirmed that I am using SparkSQL, although I am still running into the following error message:

org.apache.spark.sql.AnalysisException: Undefined function: 'regexp'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'

Generated from this query:

SELECT Name,

UniversalId,

Item_Description,

regexp(Item_Description, '\d+') AS regex_column

FROM schema.table

Re: Incorta Regex formula

JoeM — Mon, 23 Oct 2023 14:02:32 GMT

@mkrieger - I am surprised you are running into that. It might be worth also trying either regex_like or rlike.