Unlike 4D, which has only one wildcard character ("@"), SQL has multiple wildcard characters (eg. "%", "?", "_", "#", etc). SQL uses wildcard characters to substitute one or more characters in a string. See here for list of SQL wildcard characters. SQL wildcards are predominently used with the LIKE operator and WHERE clause to search for a specified pattern in a column.

When querying for strings which contain SQL wildcard characters using the WHERE/LIKE operator in 4D SQL, the wildcard characters must be escaped to be treated as a literal character. The ESCAPE clause is supported in the LIKE operator to indicate the escape character.

For example, say the data to query contains "Customer_1", "Customer_11", "Employee_1", and "Employee_11". The goal is to query for all strings that end with "_1".

Since the underscore ("_") is one of the wildcard characters in SQL, it must be escaped to be treated as a literal character. This can be written as such.

ARRAY TEXT($results; 0) Begin SQL    SELECT ID from Table_1    WHERE Field_2 LIKE '%\_1' ESCAPE '\'    INTO : $results End SQL

A backslash character ("\") is placed in front of the underscore ("_") and then the backslash is defined as the escape character. The escape character makes SQL treat the following character as a literal character.