# Convert Array to VARCHAR

**URL:** <https://community.dremio.com/t/convert-array-to-varchar/9478>\
**Category:** Uncategorized\
**Created:** [August 1, 2022, 3:53pm UTC](https://community.dremio.com/t/convert-array-to-varchar/9478 "2022-08-01T15:53:55Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![anthonyp](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@anthonyp](https://community.dremio.com/u/anthonyp)\
**Post date:** [August 1, 2022, 3:53pm UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/1 "2022-08-01T15:53:55Z")

</div>

I have an array in a View.

1. I need to convert it to a VARCHAR so I can use COALESCE function **OR** can I use Coalesce against an array element directly?

2. Would still like to convert array element to VARCHAR so I could use CASE statement.

**Tried**  
select CAST (USE\_OF\_PROCEEDS [0] as VARCHAR)  
FROM  
“External\_Data.stage0.3529”.fixedIncomeExtNamrV2\_history  
where USE\_OF\_PROCEEDS is not null

**Response:**  
SQL Error: VALIDATION ERROR: Cast function cannot convert value of type RecordType(VARCHAR(65536) useOfProceeds) to type VARCHAR(65536)

SQL Query select CAST (USE\_OF\_PROCEEDS [0] as VARCHAR)

FROM

“External\_Data.stage0.3529”.fixedIncomeExtNamrV2\_history

where USE\_OF\_PROCEEDS is not null

---

<div class="post-metadata">

**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\
**Post date:** [August 2, 2022, 5:27am UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/2 "2022-08-02T05:27:22Z")

</div>

What is the contents of the USE\_OF\_PROCEEDS column? Maybe you need to specify the item from the struct… such as USE\_OF\_PROCEEDS[0][‘useOfProceeds’]

You can also use the **TYPEOF** SQL function to confirm the datatype returned in a projection column.

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [August 10, 2022, 5:17am UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/3 "2022-08-10T05:17:43Z")

</div>

@anthonyp Do this

If the column `use_of_proceeds` shows a list icon like the below screenshot, you need to first unnest using UI or in SQL use `flatten`

```auto
flatten(balances) AS balances

```

![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/5/573fac24e1289d9b07bbd02bf016ded32e9b791e.png)

If it is a struct (see icon), then you can directly extract the element by clicking the three dots inside the field (…) or use SQL function like below

`balances.balanceusdeamount`

---

<div class="post-metadata">

**Author:** ![anthonyp](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@anthonyp](https://community.dremio.com/u/anthonyp)\
**Post date:** [October 14, 2022, 3:14am UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/4 "2022-10-14T03:14:21Z")

</div>

Apologies for the delay in responding, just back in the office.

**Dremio WEB GUI displays a value of** “WyB7CiAgInVzZU9mUHJvY2VlZHMiIDogIkdlbmVyYWwgQ29ycG9yYXRlIFB1cnBvc2VzIgp9IF0=”

**DBeaver (a SQL Client)** displays the value as [{ “useOfProceeds” : “General Corporate Purposes” }, { “useOfProceeds” : “Refinance” }]

TYPEOF (USE\_OF\_PROCEEDS) gives me VARBINARY. Based on the values, it appears to be a List or Array in binary form.

Any suggestions on how to convert this so I get a VARCHAR or at least usable Array type?

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 18, 2022, 5:17am UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/5 "2022-10-18T05:17:08Z")

</div>

@anthonyp Got it now, what source is this?

---

<div class="post-metadata">

**Author:** ![anthonyp](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@anthonyp](https://community.dremio.com/u/anthonyp)\
**Post date:** [October 18, 2022, 12:47pm UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/6 "2022-10-18T12:47:29Z")

</div>

Its from a .parquet file.

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 19, 2022, 4:37am UTC](https://community.dremio.com/t/convert-array-to-varchar/9478/7 "2022-10-19T04:37:38Z")

</div>

@anthonyp

I want to try some commands, are you able to send a portion of the Parquet file (like sample data)?