Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

punam.kumari_185226 avatar image
punam.kumari_185226 asked ·

How can I filter rows where the column value is not empty?

I have table with text column and we can have empty values in that column. I want to filter data where column is not empty. How can we do this.

CREATE TABLE Test ( pk text, cck text, data text, PRIMARY KEY(pk,cck));

Select pk, cck from Test where pk='something' and data <> '' Allow Filtering

cassandra
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered ·

@punam.kumari_185226 What you want to achieve looks strange to me but in any case, here's an answer.

Using your table schema, these are the contents of my test table:

cqlsh> SELECT * FROM emptycol WHERE pk = 'tom';

 pk  | cck     | data
-----+---------+--------
 tom |         |    red
 tom | charlie |  green
 tom |   delta |   blue
 tom |    echo | indigo
 tom | foxtrot |       
 tom |    golf |       
 tom |    kilo |       

Here's the query for the rows where data is "not empty":

cqlsh> SELECT * FROM emptycol WHERE pk = 'tom' AND data > '' ALLOW FILTERING ;

 pk  | cck     | data
-----+---------+--------
 tom |         |    red
 tom | charlie |  green
 tom |   delta |   blue
 tom |    echo | indigo

WARNING - We don't recommend you do something like this in a production environment since the performance of a query like this can be unpredictable. Cheers!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.