SQL to CQL: Pattern Matching & Search

SQL CQL

  • Pattern matching limited to LIKE and regex functions

  • Full-text search requires specialized extensions

  • Complex patterns may require multiple JOINs or subqueries

  • Native support for powerful regex and pattern matching

  • Full-text search built into the core language

  • Pattern extraction and parsing integrated into query flow

For example, the LIKE syntax allows for matching text ising a glob-like pattern matching structure and some implementations support regular expressions, XML, JSON and other functions for parsing and extracting data. Matching is always performed against the fixed structure of the table data.

LogScale supports a flexible glob-based text matching that works on extracted fields and the original raw string of the original event. In addition, JSON, XML and regular expression based extraction, selection and filtering are supported.

For example, in SQL:

sql
SELECT * FROM logs 
WHERE message LIKE '%error%' 
AND message REGEXP '\\d{3} error code';

In LogScale the equivalent query might look like:

logscale
"*error" 
| regex("error code (?:<error>/\d{3})")

The the table “SQL vs. CQL Pattern Matching Reference” contains a list of different pattern and conditional matching options.

Table: SQL vs. CQL Pattern Matching Reference

Operation SQL CQL Notes
Basic Equality WHERE column = 'value' column="value" CQL uses quotes for string values.
Inequality WHERE column != 'value' column!="value" Also can use column<>"value" in CQL.
Greater Than WHERE number > 100 number>100 Numeric comparisons work similarly.
Less Than or Equal WHERE number <= 100 number<=100  
Multiple Conditions (AND) WHERE col1 = 'val1' AND col2 = 'val2' col1="val1" col2="val2" CQL implicitly uses AND when conditions are adjacent.
Multiple Conditions (OR) WHERE col1 = 'val1' OR col2 = 'val2' col1="val1" OR col2="val2" OR must be explicit in CQL.
Grouping Conditions WHERE (col1 = 'val1' OR col1 = 'val2') AND col2 = 'val3' (col1="val1" OR col1="val2") col2="val3" Parentheses work similarly.
NULL Check WHERE column IS NULL column=null  
NOT NULL Check WHERE column IS NOT NULL column!=null  
LIKE (starts with) WHERE column LIKE 'prefix%' column="prefix*" or column=/^prefix/ CQL supports both wildcards and regex.
LIKE (ends with) WHERE column LIKE '%suffix' column="*suffix" or column=/suffix$/  
LIKE (contains) WHERE column LIKE '%text%' column="*text*" or column=text Simple contains can omit wildcards.
LIKE (pattern) WHERE column LIKE 'a%b_c' column="a*b?c" * is multi-char wildcard, ? is single-char
NOT LIKE WHERE column NOT LIKE '%text%' column!="*text*"  
REGEXP WHERE column REGEXP '^[0-9]{3}-[0-9]{2}' column=/^[0-9]{3}-[0-9]{2}/ CQL uses /pattern/ syntax for regex.
Case Insensitive WHERE LOWER(column) = 'value' column=~/value/i i regex flag for case-insensitive match.
IN List WHERE column IN ('val1', 'val2', 'val3') in(column,values=["val1", "val2", "val3"]) CQL uses square brackets for lists.
NOT IN List WHERE column NOT IN ('val1', 'val2') !in(column,values=["val1", "val2"])  
String Concatenation WHERE column = 'prefix' || 'suffix' column=concat("prefix", "suffix") Use concat() function in CQL.
Substring WHERE SUBSTRING(column, 1, 3) = 'abc' column=/^abc/ or substring(column, 0, 3)="abc" Regex often preferred.
Date Comparison WHERE date_col > '2026-01-01' date_col > comptime where comptime has been parsed as a value.  
Relative Time WHERE timestamp > NOW() - INTERVAL '1 day' test(@timestamp > now() - duration("1d")) CQL has built-in relative time syntax.
Time Range WHERE timestamp BETWEEN NOW() - INTERVAL '1 hour' AND NOW() Set the time interval for the query CQL can use time range as initial filter.
Multiple Pattern Match WHERE column LIKE '%error%' OR column LIKE '%warning%' in(column,values=["*error*", "*warning*"]) CQL supports pattern lists.
Numeric Range WHERE (number >= 100 AND number <= 200) number >100 and number < 200] CQL supports range syntax.
Existence Check WHERE column IS NOT NULL AND TRIM(column) != '' column=* Checks if field exists and has value.
JSON Path Varies by DB: WHERE JSON_EXTRACT(data, '$.key') = 'value' data.key="value" CQL uses dot notation for nested fields.
Complex Pattern Multiple LIKE/REGEXP with AND/OR column=/pattern1/ OR column=/pattern2/ CQL regex can be combined with boolean logic.
Negative Regex WHERE column NOT REGEXP 'pattern' !regex(field=column,"pattern"  
Word Boundary WHERE column REGEXP '\bword\b' column=/\bword\b/ Regex word boundaries work similarly.
IP Address Range Custom functions ip_address=cidr("10.0.0.0/24") CQL has CIDR notation support.
Multiple Field Search WHERE col1 LIKE '%text%' OR col2 LIKE '%text%' "text" Bare terms search across all fields.
Exact Phrase WHERE column = 'exact phrase' column="exact phrase"  
Boolean Field WHERE is_enabled = TRUE is_enabled=true Boolean values not quoted.
Nested Field Array Complex JSON functions user.roles=["admin"] Dot notation for nested structures.