SYNTAX_ERROR: line 9:3: Function regexp_substr not registered OR mismatched input ','. Expecting:

Question

Hi all,

I'm trying to use the function REGEXP_SUBSTR but I'm getting different errors depending on the way how I use it. Could someone have a look, or is this function just not supported by AWS ? I've tried the following ways:

, REGEXP_SUBSTR(wc.da_waarnemingsequence,( 'B01[^.]*')) trailingsequence
, REGEXP_SUBSTR(wc.da_waarnemingsequence,( 'B01[^.]*') [,1 [, 1 [, c ] ] ]) trailingsequence
, REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )

The next line of code is new line also using the REGEXP

--, REGEXP_REPLACE(REGEXP_SUBSTR(wc.da_waarnemingsequence, 'B01[^.]*'), '[B01|G[0-9]{2}|A[0-9]{2}|V[0-9]{2}]') lastexplicitscan

rePost-User-3337999 · Accepted Answer

The above error will occur if you try to use features not supported by Athena as described in this document [1]. In your case Athena is complaining about the regexp_substr function used in your query.

See the list of Presto features supported by Athena :- 
https://docs.aws.amazon.com/athena/latest/ug/presto-functions.html

I looked for the "regexp_substr" function and found it to be an Oracle/PLSQL function. We recommend using an alternative regular expression function supported by Presto, such as regexp_extract. See [2] for a list of whether Presto supports regular expression functions. Alternatively, you can use custom functions in Athena to achieve your use case. See [3] which explains how to create and use custom functions in Athena.

References:

[1]https://aws.amazon.com/premiumsupport/knowledge-center/athena-syntax-function-not-registered/

[2]https://docs.aws.amazon.com/athena/latest/ug/presto-functions.html

[3]https://docs.aws.amazon.com/athena/latest/ug/querying-udf.html

SYNTAX_ERROR: line 9:3: Function regexp_substr not registered OR mismatched input ','. Expecting: <expression>

Add your answer

Relevant content