4

I use Cassandra 3.7 and have a text column with SASI index. Let's assume that I want to find column values that contain '%' character somewhere in the middle. The problem is that '%' is a command char for LIKE clauses. How to escape '%' char in a query like LIKE '%%%'?

Here is a test script:

DROP keyspace if exists kmv;
CREATE keyspace if not exists kmv WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor':'1'} ;
USE kmv;
CREATE TABLE if not exists kmv (id int, c1 text, c2 text, PRIMARY KEY(id, c1));
CREATE CUSTOM INDEX ON kmv.kmv  ( c2 ) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {
'analyzed' : 'true',
'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
'case_sensitive' : 'false', 
'mode' : 'CONTAINS'
};  

INSERT into kmv (id, c1, c2) values (1, 'f22', 'qwe%asd');  
SELECT c2 from kmv.kmv where c2 like '%$$%$$%';

The select query returns nothing.

Ko-Chih Wu
  • 682
  • 7
  • 11
KMV
  • 51
  • 1
  • 3

2 Answers2

1

I think you can use the $$ syntax to achieve this. Your where clause would be:

LIKE '%$$%$$%'

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

gsteiner
  • 776
  • 5
  • 20
  • Unfortunately it doesn't work. The query still returns no results. P.S. I've added a test script to the question. – KMV Sep 07 '16 at 14:02
  • Aww, perfect. I'll mess around with this schema if I have time today and change my answer. – gsteiner Sep 08 '16 at 14:28
0

The percent sign is treated as special characters only at the beginning and the end. So LIKE '%%%' works fine for your case.

cqlsh:kmv> SELECT c2 from kmv.kmv where c2 like '%%%';

 c2
----------
 qwe%asd

Looking at the source, however, I don't think there is a way to escape the percent sign if it's the first or the last character, which means you can't do like queries to find values that start with %.

Ko-Chih Wu
  • 682
  • 7
  • 11
  • Thanks for the reply. Rather strange limitations. I guess it is either bugs or should be mentioned in official docs if it's an expected behaviour. https://issues.apache.org/jira/browse/CASSANDRA-12573 https://issues.apache.org/jira/browse/CASSANDRA-12621 – KMV Sep 14 '16 at 12:10