Filtering on words, phrases, or substrings

Find rows that contain words, phrases, or substrings in indexed fields. (Similar to LIKE in SQL.)

Find rows that contain words, phrases, or substrings in indexed fields, similar to LIKE in SQL.
  • Term: A word that contains no spaces or punctuation and is separated from other content by a beginning or end of line, space, or punctuation mark.
  • Substring: Match character patterns in a term. Use asterisk (*) for zero or more characters. Use question mark (?) for zero or one character in a term search.
  • Phrase: Exact string that contains spaces or punctuation or both. Wrap phrases in double-quotes to search for the complete string when separated from other content by a space, punctuation mark, or beginning or end of a line.

Prerequisites

To run the following examples, set up Term and phrase searches using the wikipedia demo. Use cqlsh on a search node and replace the search_expression in the following statement with the example string.
SELECT count(*) FROM wiki.solr
WHERE solr_query = search_expression;

Procedure

  • Search for a single term on any indexed column or a specific column:
    Table 1. Word examples
    Search in Syntax Example Results Description
    Any column 'term' 'Journal'
    count
    -------
       159
    Count of rows that contain the word African in the title or body fields.
    Specific column 'column_name: term' 'title: Journal'
    count
    -------
         9
    Count of rows that contain the word Journal in the title. Use column names in the query syntax to limit searches to specific columns.
  • Search for substrings:

    Asterisk indicates zero or more characters.

    Question mark indicates zero or one character.

    Table 2. Substring examples
    Type Example Results Description
    Beginning of term 'title:Africa?'
    count
    -------
        16
    Count of rows that have a term that begins with Africa in the title, but can have only one additional character.
    Anywhere in term 'title:*at*'
    count
    -------
       559
    Count of rows that have the term at or a term that contains at.
  • Searching for a phrase in any column or a specific column:
    Table 3. Phrase examples
    Search in Syntax Example Results Description
    Any column '"phrase"' '"African Journal"'
    count
    -------
         8
    Count of rows that contain the complete phrase in the title or body.
    Specific column 'column_name:"phrase"' 'title:"African Journal"'
    count
    -------
         8
    Count of rows that contain the complete phrase only in the title.
  • Search for multiple words or phrases in an indexed field, such as title, using operators:
    Table 4. Multiple terms or phrases with operators
    Location Example Results Description
    Require multiple terms 'title:(+Journal, +Science)''

    'title:Journal AND Science'

    count
    -------
         4
    Count of rows that contain both Journal and Science in the title. To use a list of terms, enclose a comma or space-separated list of terms and specify a boolean operator. In this case + requires the term; therefore both terms must be in the title.
    Either term 'title:(Journal || Science)'
    count
    -------
         12
    Count of rows that contain either Journal or Science in the title. Separate the terms using two pipe characters. To search for either phrase, surround the string with double quotes '("Journal of Science" || "Science Journal")'.
    Substring beginning of term, including 'title:Africa?'
    count
    -------
        16
    Count of rows that have a term that begins with Africa in the title but can only have one additional character.
    Substring anywhere in term, including term 'title:*at*'
    count
    -------
       559
    Count of rows that have the term at or a term that contains at.
    '("Journal of Science" || "Science Journal")' Count of rows that contain either term in the title.

    To search for both phrases surround each string with double quotes.

    Phrase and either term 'title:(+"African Journal", +(Science || "Legal Studies"))'
    count
    -------
         5
    Use Solr booleans to create more complex queries; this example counts row with African Journal in the title that also contain either Science or Legal Studies.