Is there any query for Cassandra as same as SQL:LIKE Condition?

sql cassandra pycassa
BobDroid picture BobDroid · Mar 28, 2012 · Viewed 61.3k times · Source

The LIKE condition allows us to use wildcards in the where clause of an SQL statement. This allows us to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete. Like this

SELECT * FROM users
WHERE user_name like 'babu%';

like the same above operation any query is available for Cassandra in CLI.

Answer

nstrelow picture nstrelow · Jul 19, 2017

Since Cassandra 3.4 (3.5 recommended), LIKE queries can be achieved using a SSTable Attached Secondary Index (SASI).

For example:

CREATE TABLE cycling.cyclist_name ( 
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);

Creating the SASI as follows:

CREATE CUSTOM INDEX  fn_prefix ON cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then a prefix LIKE query is working:

SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cyclist_name WHERE firstname LIKE 'Mic%';

These examples and more configuration options, like suffix queries, can be found in the documentation

A more in depth explanation about how SASI works can be found here.

Related questions

How to create foreign key in Cassandra CQL

I'm trying to replicate a SQL database in Cassandra, but, while I had no problem creating the tables, I found that I cannot find an example easy to understand that shows how I can create foreign keys in Cassandra. So, …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More