AnswerBun.com

How to pull data from an array using SQL

Stack Overflow Asked by Sarah on September 29, 2020

If I have a string column with the following data:

+-------+----------------------------+
| Row # |        Array Column        |
+-------+----------------------------+
|     1 | {apple;coconut;mango}      |
|     2 | {coconut;pear;grapes}      |
|     3 | {strawberry;mango;coconut} |
|     4 | {}                         |
|     5 | {january}                  |
+-------+----------------------------+

How can I pull out only the rows that have mango or nothing in it?

Result:

+-------+----------------------------+
| Row # |        Array Column        |
+-------+----------------------------+
|     1 | {apple;coconut;mango}      |
|     3 | {strawberry;mango;coconut} |
|     4 | {}                         |
+-------+----------------------------+

One Answer

If the column is a string and not an actual array.

SELECT * 
FROM YourTable 
WHERE ArrayColumn='{}' 
OR ArrayColumn like '%mango%'

If the column is indeed an array

SELECT * 
FROM YourTable 
WHERE size(ArrayColumn)=0
OR array_contains(ArrayColumn,'mango')

Answered by Gabriel Durac on September 29, 2020

Add your own answers!

Related Questions

Replace duplicates items in array with different values

3  Asked on November 7, 2021 by mohamed-eshaftri

         

How to fix object “x” not found

2  Asked on November 7, 2021 by ronie-febriansah

 

link error when using random_device from boost

2  Asked on November 7, 2021 by dikiidog

     

Error: invalid input syntax for type integer

0  Asked on November 7, 2021 by hammad-ali

     

android studio : My app crashes immediately after start

5  Asked on November 7, 2021 by user13930404

   

Int + Str type python

1  Asked on November 7, 2021

     

Javascript Output is giving empty response

0  Asked on November 7, 2021 by manish-jha

     

Create many CSV Files based on Pandas df column value

2  Asked on November 7, 2021 by eithar

       

How to change radio button checked attribute

2  Asked on November 7, 2021 by white-death

       

Vue js/Javascript Objects.assign() not working

0  Asked on November 7, 2021 by madsongr

     

Problems with HTTP/2 on nginx for Windows?

0  Asked on November 7, 2021 by camaross

     

Error to start application after insert Entity Framework

1  Asked on November 7, 2021 by bircastri

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP