arithmetic in copy command

user_14049909 0
Viewed 16

I am trying to perform some transformation in the COPY command using below; however, getting errors like >"Function '-' not supported within COPY"

it is complaining about the arithmetic operation/minus sign in the brackets.

Thanks in advance for your comments.

split('lemon  orange','  ')[array_size(split('lemon  orange','  '))-1]
snowflake-cloud-data-platform command copy
1 Answers

So this can be done without the ARRAY_SIZE or subtraction (in a SELECT command)

SELECT
    'lemon  orange' as in_str,
    split(in_str,'  ') as s,
    s[array_size(s)-1] as last_item_a,
    reverse(in_str) as r_in_str,
    split(r_in_str, '  ') as rs,
    rs[0] as r_last_b,
    reverse(r_last_b) as last_b
    ;
IN_STR S LAST_ITEM_A R_IN_STR RS R_LAST_B LAST_B
lemon orange [ "lemon", "orange" ] "orange" egnaro nomel [ "egnaro", "nomel" ] "egnaro" orange

OR in one line

SELECT
    'lemon  orange' as in_str,
    split(in_str,'  ')[array_size(split(in_str,'  '))-1]::text as last_a,
    reverse(split(reverse(in_str), '  ')[0]) as last
    ;

this time I added a ::text to your method as the output from the prior run show double quotes in the WebUI which show it was still being treated as a VARIANT data type, which can upset other data processing functions like TO_DATA or TO_NUMBER some times.

IN_STR LAST_A LAST
lemon orange orange orange

I have not checked to see if the works via a COPY.. though

COPY INTO @~/copy_test.csv FROM (SELECT 'lemon  orange'::text as in_str);   

CREATE TABLE copy_to_test(single_string text);

COPY INTO copy_to_test FROM (
    SELECT split($1,'  ')[array_size(split($1,'  '))-1]::text FROM @~/copy_test.csv);
    
-- 002300 (0A000): SQL Compilation error: Function '-' not supported within a COPY

COPY INTO copy_to_test FROM (
    SELECT reverse(split(reverse($1), '  ')[0]) FROM @~/copy_test.csv);
file status rows_parsed rows_loaded error_limit errors_seen first_error first_error_line first_error_character first_error_column_name
copy_test.csv_0_0_0.csv.gz LOADED 1 1 1 0 
SELECT * FROM copy_to_test;
SINGLE_STRING
orange
user_439920
Related