Regexp matching with dynamic pattern


I want to match a string with a pattern in a join condition, using the extract_pattern function provided by the assistance.
I receive an error in the server.log file that indicates that the pattern must be a constant !

Any idea if there could be a way to do rexgexp match/capture with a dynamic pattern using Dremio ?


@dfleckinger, can you provide and example of the match you are trying to do and the error message from the logs? Is there a query profile generated? If so, kindly share that as well.


here is an example of a query where i want to identify the brand of a product given its productName, knowing that the products belong to an account whom I know a list of potentiel possible brands.

select products.productName,
extract_pattern(products.productName, accounts.brand_pattern, 0, ‘CAPTURE_GROUP’) AS brand_found
inner join account_brands on products.account_id = account_brands.account_id
products.productName is not null

for instance brand pattern can be something such as ‘.(\b(Apple|Samsung|Huawei)\b).

There query results in an error :

IllegalArgumentException: The argument ‘pattern’ of Function ‘extract_pattern’ has to be constant!

Query profile associated : (9.9 KB)

I still receive following exception when using REGEXP_LIKE or REGEXP_MATCHES in the context of join with a pattern depending on another table content.
IllegalArgumentException: The argument ‘pattern’ of Function ‘regexp_like’ has to be constant!