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?

Which dbms are you using?

jarlh
– jarlh

2023-03-18 16:08:52 +00:00
Commented Mar 18, 2023 at 16:08 — jarlh
– jarlh, Commented Mar 18, 2023 at 16:08
Athena presto sql

Philo
– Philo

2023-03-18 16:11:03 +00:00
Commented Mar 18, 2023 at 16:11 — Philo
– Philo, Commented Mar 18, 2023 at 16:11
Can you please add sample data and desired output?

Guru Stron
– Guru Stron

2023-03-19 13:09:44 +00:00
Commented Mar 19, 2023 at 13:09 — Guru Stron
– Guru Stron, Commented Mar 19, 2023 at 13:09

Abdulmajeed · Accepted Answer · 2023-03-18 17:19:15Z

0

Try this:

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

answered Mar 18, 2023 at 17:19

Abdulmajeed

1,5452 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related