How to access sql column that is in a map,string,struct,array,string

Question

I have a table in sql that is map,string,struct,array,string. I have accessed the value by using element_at(k,v) which works. My issue is the column has values like '#"nike"#','#"REEBOK"#'. I have tried stripping the characters(,#), I have tried regex_extract and regex_replace, but I keep getting the error:

Unexpected parameters(map(varchar(row(input varchar(input(array(varchar),input,textinput() expected element_at(array(E), element_map(K,V),K)K,V

SELECT 
    REGEXP_REPLACE(col, '"[^"]*"', '')
FROM table;

is there a way to strip or extract the words in between the " " characters?

Answer 1

Try this:

SELECT
    REGEXP_REPLACE(col, '[#"]', '') AS cleaned_col
FROM table;