Error, Substring not taking Varchar/Regex as Param

Hey, i have a field which includes the currency and the value (EUR 9.00).
In the docs i found SUBSTRING(string_expression varchar, pattern varchar) which takes an varchar expression.

I created this query:

SELECT
SUBSTRING(Amazon.get_fba_inventory_aged_data."your-price", '[0-9]+') as "your_price",
Amazon.get_fba_inventory_aged_data.sku as "sku",
Amazon.get_fba_inventory_aged_data."product-name" as "product_name"
FROM Amazon.get_fba_inventory_aged_data

But i get this error message:

Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<VARCHAR(65536)> FROM <VARCHAR(6)>)'. 
Supported form(s): 
'SUBSTRING(<CHAR> FROM <INTEGER>)' 
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)' 
'SUBSTRING(<VARCHAR> FROM <INTEGER>)' 
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)' 
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)' 
'SUBSTRING(<VARBINARY> FROM <INTEGER>)' 
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

Seems like substring cant take an regex expression or am i missing something?

So i guess the documentation is outdated?

I dont want to be rude/annoying or anything, but i would really appreciate a reply wether the documentation is wrong/outdated or if iā€™m missing something.

@ASchmidt

Iā€™m guessing you want to extract the price from a string like ā€œ(EUR 9.00)ā€?

See if the following works?

SELECT cast(regexp_extract('(EUR 9.00)', '(\d*\.?\d+)', 1) as FLOAT)

1 Like

Thank you for your answer.
Your solution did the job. Except i had some empty fields so i needed to convert them first.

SELECT cast(regexp_extract(your_price, '(\d*\.?\d+)', 1) as DOUBLE) as your_price
FROM
(
    SELECT
    CASE agedData."your-price"
        WHEN '' THEN '0'
        ELSE agedData."your-price"
    END as your_price
    FROM Amazon.mszweirad.get_fba_inventory_aged_data as agedData
)