Like

The like predicate specifies a pattern for string values in a where clause. The comparison with characters specified in the pattern is case sensitive; i.e. 'A' is not equal to 'a'. If a case insensitive comparison is desired, the ilike operator can be used. The pattern can be a string with or without wild card character '_' or '%'. The '%' is a placeholder for any combination of characters, or no characters. The underscore '_' accepts any character in that exact position.

Syntax

 
    select * from ... 
    where s like pattern
     
    select * from ... 
    where s ilike pattern
     

The not operator can be added to an expression using like or ilike:

 
    select * from ... 
    where s not like pattern
     
    select * from ... 
    where s not ilike pattern
     

Examples

 
    create table names(s char(20));
    insert into names values(['Smith','Jones','Black','Clark','Adams']);
    select * from names;
    s
    ------------------------------------------------------------------------------
    Smith
    Jones
    Black
    Clark
    Adams
     
    Selected records: 5
     
    select * from names where s like 'A';
    s
    ------------------------------------------------------------------------------
     
    Selected records: 0
     
    select * from names where s like 'A%';
    s
    ------------------------------------------------------------------------------
    Adams
 
    Selected records: 1
     
    select * from names where s like 'a%';
    s
    ------------------------------------------------------------------------------
     
    Selected records: 0
     
    select * from names where s ilike 'a%';
    s
    ------------------------------------------------------------------------------
    Adams
 
    Selected records: 1
     
 
    select * from names where s like '%k%';
    s
    ------------------------------------------------------------------------------
    Black
    Clark
     
    Selected records: 2
 
    select * from names where s like '%i%';
    s
    ------------------------------------------------------------------------------
    Smith
 
    Selected records: 1
 
    select * from names where s like '%a%';
    s
    ------------------------------------------------------------------------------
    Black
    Clark
    Adams
 
    Selected records: 3
 
    select * from names where s not like '%a%';
    s
    ------------------------------------------------------------------------------
    Smith
    Jones
 
    Selected records: 2
     
    select * from names where s ilike '__a%';
    s
    ------------------------------------------------------------------------------
    Black
    Clark
    Adams
 
    Selected records: 3
     
    select * from s where s ilike '_o%';
    s
    ------------------------------------------------------------------------------
    Jones
     
    Selected records: 1
     

For more examples please see xSQL SDK sample like.

Usage with Sequence Fields

The like operator can also be used for comparison on sequence type string fields. For example, consider the following table definition and initialization:

 
    create table tt(i sequence(int), j sequence(char(20)));
    insert into tt values(1, ['Smith','Jones','Black','Clark','Adams']);
     

We could now perform select statements like the following:

     
    select j = 'Adams' from tt;
    #1
    ------------------------------------------------------------------------------
    {0, 0, 0, 0, 1}
 
    Selected records: 1
     

This select returns a bit mask for the sequence j, marking with 1 the elements for which the equals condition is true. Similarly we can use the like operator as follows:

     
    select  j like 'A%';
    #1
    ------------------------------------------------------------------------------
    {0, 0, 0, 0, 1}
 
    Selected records: 1
     

Again, this select returns a bit mask for the sequence j, marking with 1 the elements for which the like condition is true. Another variant using the like operator is as follows:

     
    select  * from tt where j like 'A%';
    i j
    ------------------------------------------------------------------------------
    {1} {Smith, Jones, Black, Clark, Adams}
 
    Selected records: 1
     

This select searches for all tt records where some element of sequence j starts with 'A'.