Apollo SQL > DDL & DML Statements

The operator LIKE is more flexible than operator '=', because LIKE can do a more powerful selection using wildcards. The two wildcards characters are % (percent) and '_' (underline). The percent symbol designates any number of characters and the underlined character represents a single character. They can be combined in an expression and repeated as many times as you wish.

ESCAPE syntax is supported when using LIKE. Use ESCAPE when the wildcard character "%" or "_" appear as data in the column. The ESCAPE keyword designates an escape character. In the comparison value for the LIKE predicate, the character that follows the escape character is treated as a data character and not a wildcard character. Other wildcard characters in the comparison value are unaffected.

In the example below, the "^" character is designated as the escape character. In the comparison value for the LIKE predicate ("%10^%%"), the "%" that immediately follows the escape character is treated as data in the PercentValue. This allows filtering based on the string "10%".

SELECT * FROM Sales WHERE (PercentValue LIKE "%10^%%" ESCAPE "^")

Using LIKE, the following statements could be written easier:

SELECT * FROM customer WHERE LEFT(firstname, 2) = 'Al';

could be:

SELECT * FROM customer WHERE firstname LIKE 'Al%'

SELECT * FROM Customer WHERE Right(firstname, 2) = 'Al';

could be:

SELECT * FROM customer WHERE firstname LIKE '%Al'

SELECT * FROM customer WHERE Pos('Al', firstname) > 0

could be:

SELECT * FROM customer WHERE firstname LIKE '%Al%'

SELECT * FROM customer WHERE (LEFT(firstname,1) = 'C') And

(RIGHT(firstname,1) = 'n');

•could be:

SELECT * FROM customer WHERE lastname LIKE 'C%n';

Additional examples using WHERE:

SELECT * FROM customer WHERE firstname LIKE '%ZAMO%'

SELECT * FROM customer WHERE firstname LIKE 'b_n'

SELECT * FROM customer WHERE firstname LIKE 'b_n%zuela%diaz'

SELECT * FROM customer WHERE county IN

('MADRID', 'BARCELONA', 'VALENCIA','TOLEDO', 'AVILA');

SELECT * FROM customer WHERE county NOT IN ('MADRID', 'BARCELONA');

SELECT * FROM customer WHERE CustNo IN (1317, 1456, 2376, 1318);

SELECT * FROM Customer c, Orders o, Items i, Parts p

WHERE (c.CustNo = o.CustNo) AND (o.OrderNo = i.OrderNo) AND

(i.PartNo = p.PartNo) AND (c.CustNo > 1300) AND (c.CustNo < 2000);

LIKE (SELECT)

LIKE (SELECT)