Sql – ny query for Cassandra as same as SQL:LIKE Condition


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

WHERE user_name like 'babu%';

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

Best Solution

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 ( 
  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 Question