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

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.


I know: It's a old question but there is a solution for this topic:

You can't use like operator in cassandra but you can use range operators and with the range operator you can solve this "like 'whatever%'"

An example: I have more than one product. Each product has his own partition key (first part of the primary key):

CREATE TABLE user(productId int, username text, PRIMARY KEY(productId, username));

Now i have some users:

INSERT INTO user(productId, username) VALUES (1, 'anna');
INSERT INTO user(productId, username) VALUES (1, 'alpha');
INSERT INTO user(productId, username) VALUES (1, 'andreas');
INSERT INTO user(productId, username) VALUES (1, 'alex');
INSERT INTO user(productId, username) VALUES (1, 'bernd');
INSERT INTO user(productId, username) VALUES (1, 'bob');

Now, i want to find all users which have an a at the beginning. In a SQL world i use LIKE 'a%' in Cassandra i use this:

SELECT * FROM user WHERE productId = 1 AND username >= 'a' AND username < 'b';

The result:

productid | username
-----------+----------
     1 |     alex
     1 |    alpha
     1 |  andreas
     1 |     anna

Simple answer: there is no equivalent of LIKE

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlSelect.html

Here is the command reference for v0.8:

http://www.datastax.com/docs/0.8/references/cql#cql-reference

If you maintain another set of rows that hold references to a username:

row: username:bab -> col:babu1, col:babar row: username:babu -> col:babur

Effectively you are cheating by pre-populating all of the results that you would normally search with in the RDBMS world. Storage is cheap in comparison to what it was years ago ... which is why this is now an accepted approach. It's less intensive on the CPU and Memory to retrieve a pre-populated list of information.