That depends on the database that is queried. See this comparison:
Wildcard comparison for the SQL LIKE operator by database vendor/standard
| Wildcard |
Description |
H2 |
MySql |
MSSql |
Oracle |
ANSI-89 |
ANSI-92+ |
| % |
Any string of zero or more characters. |
X |
X |
X |
X |
|
X |
| _ |
Any single character. |
X |
X |
X |
X |
|
X |
| [] |
Any single character within the specified range ([a-f]) or set ([abcdef]). |
|
|
X |
|
X |
X |
| [^] |
Any single character not within the specified range ([^a-f]) or set ([^abcdef]). |
|
|
X |
|
|
X |
| [!] |
Any single character not within the specified range ([^a-f]) or set ([^abcdef]). |
|
|
|
|
X |
|
| * |
Any string of zero or more characters. |
|
|
|
|
X |
|
| ? |
Any single character. |
|
|
|
|
X |
|
| \ |
Default escape character. |
X |
X |
|
|
|
|
| # |
Matches any single numeric character. |
|
|
|
|
X |
|
| ESCAPE |
Clause to identify the escape character |
X |
X |
X |
X |
|
|
If you need more complex string comparisons you might look into using regular expressions in queries or iterate over unfiltered results in code. Note: Not all database vendors implement regular expressions in queries. Those that do vary widely and it will probably reduce performance. The table below is by no means complete but it demonstrates the different implementations.
| Function |
H2 |
MySql |
MSSql |
Oracle |
ANSI-99 |
| REGEXP_INSTR |
|
|
|
X |
|
| REGEXP_LIKE |
|
|
|
X |
|
| REGEXP_REPLACE |
|
|
|
X |
|
| REGEXP_SUBSTR |
|
|
|
X |
|
| NOT_REGEXP |
|
X |
|
|
|
| REGEXP |
|
X |
|
|
|
| RLIKE |
|
X |
|
|
|
| SIMILAR TO |
|
|
|
|
X |
| Regex.IsMatch |
|
|
X |
|
|
| Regex.Match |
|
|
X |
|
|
| Regex.Matches |
|
|
X |
|
|
| Regex.Replace |
|
|
X |
|
|
| Regex.Split |
|
|
X |
|
|
| ~ (POSIX Operator) |
|
|
X |
|
|
Examples of wildcards used in a QIE Interface Engine script that queries a database:
// Example: last equals PID-5.1
var queryResult = qie.doQuery("CentricityPSDbConn", "SELECT patientId FROM patientprofile WHERE last = '{PID-5.1}' AND first = '{PID-5.2}' AND birthdate = '{PID-7}'");
message.setNode("PID-18", queryResult.getNode("patientId"));
// Example: last ends with PID-5.1
var queryResult = qie.doQuery("CentricityPSDbConn", "SELECT patientId FROM patientprofile WHERE last LIKE '{PID-5.1}%' AND first = '{PID-5.2}' AND birthdate = '{PID-7}'");
message.setNode("PID-18", queryResult.getNode("patientId"));
// Example: last starts with PID-5.1
var queryResult = qie.doQuery("CentricityPSDbConn", "SELECT patientId FROM patientprofile WHERE last LIKE '%{PID-5.1}' AND first = '{PID-5.2}' AND birthdate = '{PID-7}'");
message.setNode("PID-18", queryResult.getNode("patientId"));
// Example: last contains PID-5.1
var queryResult = qie.doQuery("CentricityPSDbConn", "SELECT patientId FROM patientprofile WHERE last LIKE '%{PID-5.1}%' AND first = '{PID-5.2}' AND birthdate = '{PID-7}'");
message.setNode("PID-18", queryResult.getNode("patientId"));
