Over the years, I’ve noticed that certain SQL questions come up repeatedly on the InterSystems Developer Community, especially about using the LIKE predicate in different contexts. Common variations include:
and many more derivatives. So, I decided to write an article that focuses on how LIKE works in InterSystems IRIS SQL, especially when used with variables in Embedded SQL, Dynamic SQL, and Class Queries, while touching on pattern escaping and special character searches.
First of all, I'd like to mention that InterSystems IRIS SQL offers most of the capabilities available in other relational DBMS that implement a later version of the SQL standard. But at the same time, it's important to mention that apart from relational access, in IRIS you can also use other models to get the same data, for example, object or document models.
On this note, let's look at the LIKE predicate and how this tool is used in SQL for pattern matching.
Basic info
Firstly, let's look at some basics. The LIKE predicate is used in a WHERE (or HAVING) clause of a SELECT, UPDATE, or DELETE statement to filter records based on whether a column's values match a specified pattern. The basic syntax is as follows:
SELECT column1, column2
FROM table_name
WHERE column_name LIKE pattern;
The pattern can include literal characters and two primary wildcard characters:
- % (percent sign): represents zero, one, or multiple characters. For example, 'A%' matches any string starting with 'A', '%Z' matches any string ending with 'Z', and '%XY%' matches any string containing 'XY'.
- _ (underscore): represents a single character. For example, 'A_C' matches 'ABC', 'AEC', etc., but not 'AC' or 'ABBC'.
For example, the following query retrieves all names from the Employees table that start with "Jo":
SELECT Name
FROM Employees
WHERE Name LIKE 'Jo%';
And this query retrieves all names that have an "a" as the second character:
SELECT Name
FROM Employees
WHERE Name LIKE '_a%';
It's crucial to understand that LIKE performs pattern matching, not equality. While 'ABC' LIKE 'ABC' evaluates to true, it's generally more efficient to use the = operator for exact string matches ('ABC' = 'ABC'). LIKE excels when you need fuzzy matching or searches for substrings.
Include a special character in a search phrase (escape clause)
I find the ESCAPE clause to be quite handy when you need to use a wildcard character, e.g. %, in your LIKE statement, and want it actually to mean a percentage sign. In this case, you can use the ESCAPE clause to define an escape character. Any character immediately following the escape character in the pattern is treated as a literal character, not a wildcard. For example, if you have the following data:
INSERT INTO Post.Promos(name, description) VALUES('Test 1', 'This is 40% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 2', 'This is 50% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 3', 'This is 10% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 4', 'some description')
and you wish to find all Promos that have a "50% discount" phrase in the description, you can use the following query:
SELECT Name
FROM Post.Promos
WHERE Description LIKE '%50\% discount' ESCAPE '\'
In this example, \ is defined as the escape character, so the system will treat the % sign as a literal, not a wildcard, and will look for a discount with a description that contains the literal phrase "50% discount".
Find special characters
If you need to look for several special characters or to find all of them in a string, you can't use a LIKE predicate, you need to use %MATCHES. It matches a value with a pattern string containing literals, wildcards, and ranges. Beware that a pattern should be specified in Logical format, regardless of the %SelectMode setting. So if you want to find all values that contain any special character, you can use %MATCHES to look for them like this:
SELECT *
FROM Post.Promos p
WHERE p.description %MATCHES '*[!”%#$&”()*+,-./:;<=>?@\%\_]*'
This finds any description containing at least one of the listed symbols. You can use the ESCAPE clause to specify the escape character, but by default it's set to "\", so you can omit it as is done in my example.
The query above will return three rows with 40%, 50% and 10% discount and not 'some description'.
Also, this predicate follows the general rules of pattern matching of IRIS. For example, for a placeholder, you will use "?" and not "_":
SELECT *
FROM Post.Promos p
WHERE p.name %MATCHES '???? [0-9]'
This will look for all names that consist of any four characters, a space, and a numerical digit.
While I'm on the topic of patterns, there's also a predicate %PATTERN that allows you to match a pattern of character type codes and literals to the data values. So, to perform the same search as above, you can write the following query:
SELECT *
FROM Post.Promos p
WHERE p.name %PATTERN '1U.L1" "1N'
This matches:
1U — 1 uppercase letter
.L — lowercase letters
1" " — 1 space
1N — 1 numerical digit
Using variables with LIKE
Now it's time to look at how you can use variables in your queries. There are three ways you can use a SQL statement in your Object Script code: Embedded SQL, Dynamic SQL, and writing a class query.
Embedded SQL
To pass a value into an Embedded SQL statement, you should use a named parameter (or, in other terms, input and/or output host variables), meaning that it has to have a name. For example, if we still wish to find all Promos with a 50% discount, we will write the following query:
set param = "50\% discount"
&sql(DECLARE C1 CURSOR FOR
SELECT Name
INTO :nameout
FROM Post.Promos
WHERE Description LIKE '%'_:param_'%' ESCAPE '\')
&sql(OPEN C1)
QUIT:(SQLCODE'=0)
&sql(FETCH C1)
WHILE (SQLCODE = 0) {
WRITE nameout,!
&sql(FETCH C1) }
&sql(CLOSE C1)
Its input host variable is param, and it equals "50% discount". To make the query understand that % is a part of the parameter and not a placeholder for any length of characters, I use the ESCAPE clause.
Also, don't misplace double and single quotation marks:
- In SQL, you use the former to mark a field that is a reserved name, for example, "Group". You use the latter to observe any string.
- In ObjectScript, you use the former to observe the string, and the latter has nothing to do with strings at all, it's an unary operator NOT.
When using parameters, you don't need to put a single quotation mark inside the double quotation mark
set param = " '50\% discount ' "
to show the compiler that it's a string or anything to this effect. In this case, the engine will look for single quotes as part of the search string,
The output host variable in the example above is "nameout," where the value of the Name column will be placed, and it can be used later in the code.
Dynamic SQL
Starting with version 2015.2, Dynamic SQL can accept a literal value input to a query in two ways:
- input parameters specified at execution time using the “?” character
- input host variables specified at prepare time
The second approach will follow the same idea as in Embedded SQL above:
set param = "50\% discount"
set myquery = 3
set tStatement = ##class(%SQL.Statement).%New()
set myquery(1) = "SELECT Name"
set myquery(2) = "FROM Post.Promos"
set myquery(3) = "WHERE Description LIKE '%'_:param_'%' ESCAPE '\'"
set qStatus = tStatement.%Prepare(.myquery)
set tResult = tStatement.%Execute()
while tResult.%Next() {
write tResult.Name, !
}
with the exception that you don't need the output host variables because the %SQL.StatementResult (result type of tStatement.%Execute()) will have all the properties that reference columns in the SELECT statement.
In the first approach, you put the question mark in place of a parameter, and then when calling %Execute(), you need to provide values for the parameters in the same order as the "?" in your statement:
set param = "50\% discount"
set myquery = 3
set tStatement = ##class(%SQL.Statement).%New()
set myquery(1) = "SELECT Name"
set myquery(2) = "FROM Post.Promos"
set myquery(3) = "WHERE Description LIKE '%'_?_'%' ESCAPE '\'"
set qStatus = tStatement.%Prepare(.myquery)
set tResult = tStatement.%Execute(param)
while tResult.%Next() {
write tResult.Name, !
}
Class Query
The input host variables are used in Class Queries following the same rules as in Embedded and Dynamic SQL:
Query GetDiscount(param As %String) As %SQLQuery [ SqlProc ]
{
SELECT Name FROM Post.Promos
WHERE (Description LIKE '%'_:param_'%' ESCAPE '\')
}
When calling the query, you provide parameters in the same order as they are written in the method signature:
SELECT *
FROM Post.Promos_GetDiscount('50\% discount')
I hope this article answers at least some of the popular questions that crop up from time to time.
If you're interested in a more in-depth article about performance considerations and best practices, or if you have any comments, don't hesitate to leave them in the comments section below.
