Incorta Regex formula

[mkrieger]

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

[JoeM]

@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`

[mkrieger]

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

[JoeM]

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