Criteria examples using the LIKE operator

 

With SQL the LIKE operator allows to search for characters or strings in character fields, using wildcards such ads the question mark and the star. (These characters will be translated into SQL with an underscore for the question mark and a % for the star).

 

Three operators "Begin with", "Ending with" and "Including" add automatically the wildcard "star" according the situation:

 

Operator

Argument entered

Display

SQL Translation

Begin with

AR

AR%

LIKE 'AR%'

Ending with

GE

%GE

LIKE '%GE'

Including

RED

%RED%

LIKE '%RED%'

 

One operator "Like" allows you to add yourself the wildcards ? and * where you need to make some customized search taking care about the position of the argument, as describe in the following examples:

 

Warning: in some case an escape character is required if you look for an SQL reserved character such as the percent %, the star *, the question mark ? or the underscore character _ or also the left square bracket [, the last one is used by SQL Server. The reserved characters can depend on the database used.

With ClicknDECiDE Builder the escape character is the "|" (called Pipe or get by pressing Alt+124).

For example to look for the string */[%d you must use the LIKE function in the criteria with the following syntax (in red the escape character): |*/|[|%d

For example to look for the string %%%| you must use the LIKE function in the criteria with the following syntax (in red the escape character): |%|%|%||

 

Meaning

Argument entered and displayed

SQL Translation

Contains both string RED and BLACK anywhere


*RED*BLACK*


LIKE '%RED%BLACK%'

Begins with the string RED and contains BLACK anywhere


RED*BLACK*


LIKE 'RED%BLACK%'

Begins with the number 1, and contains the string 75 at position 6 and 7 (Social Security number in France for men born in Paris)


1????75*


LIKE '1____75%'

Contains the string BLUE at position 4 and is finishing with the string 2001

???BLUE*2001

LIKE '___BLUE%2001'

 

Caution: some data sources (iSeries AS/400, DRDA …) don't support the use of parameter into an expression, for example the LIKE statement. If you wish to use "Beginning with", "Including" or "Ending with" with a parameter, use the following method: you must add the HOST( ) function to the parameter name in the Expression Tab. Example: HOST(ParamName)

 

You can also use the LIKE function as follow:

 

To Whom It May Concern: replace a "Beginning with":

Use the criteria "Like" instead of "Beginning with"

In the Tab "Expression" select the parameter name, for example ParamName

Modify it with the following formula: HOST('’’§X%'’’,HOST(ParamName)) and validate

 

To replace an "Including":

Use the criteria "Like" instead of "Including".

In the Tab "Expression" select the parameter name, for example ParamName

Modify it with the following formula: HOST(‘’'%§X%'’’,HOST(ParamName)) and validate

 

To replace an "Ending with":

Use the criteria "Like" instead of "Ending with".

In the Tab "Expression" select the parameter name, for example ParamName

Modify it with the following formula: HOST(‘’'%§X'’’,HOST(ParamName)) and validate

 

To search a character in the 3rd position with a parameter:

Use the criteria "Like".

In the Tab "Expression" select the parameter name, for example ParamName

Modify it with the following formula: HOST('''__§X%''',HOST(ParamName)) and validate.

(the 2 first characters are underscore character).