Error, Substring not taking Varchar/Regex as Param

1

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?

2

So i guess the documentation is outdated?

1 Like
3

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.

4

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

image
image969×244 20.6 KB

https://docs.dremio.com/software/sql-reference/sql-functions/STRING/

1 Like
5

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
)
6

Thanks for the solution. This is my first day trying out Dremio for evaluation.

I’m glad to see the regex functionality is available, but I also expect the documentation to be helpful. Like the op, I also tried SUBSTRING() without success. Even the sample provided in the doc doesn’t work.

Are there plans to update the SQL reference documentation?

7

@ASchmidt @dhochee Yes, it should be in the documentation and I have created a ticket

1 Like