Using LIKE In Your SQL
The following article is designed to help people who are using the QueryCell add-in for Microsoft Excel.
The QueryCell Add-In uses the Firebird embedded database.
Although regular expressions are not supported at the time of writing, using the LIKE operator and the available SQL functions we can go some way to supporting sophisticated pattern matching.
Please contact us at [email protected] if you have any questions or suggestions about this tutorial.
LIKE – The LIKE operator
Hi there, We’re going to talk a little about using the LIKE operatore or predicate inside QueryCell, the add-in for Microsoft Excel.
The QueryCell add-in is based upon the Firebird embedded database and the Firebird embedded database supports the like operator or predicate along with two special characters.
The underscore character, which is a wildcard representing any single character. And the percent character which is a wildcard representing zero or more characters.
Wildcards – Underscore and Percentage Wildcard Characters
So let’s have a look at this first SQL statement.
SELECT
*
FROM
A
WHERE
A.suburb IS LIKE ‘%STEPHENS’;
When we run it we find it matches ‘PORT STEPHENS’, it also matches ‘OLD STEPHENS’ in fact it matches any string than ends in ‘STEPHENS’ and given that it represents zero or more characters it will actually match the string literal ‘STEPHENS’ itself.
The second query :
SELECT
*
FROM
A
WHERE
A.suburb IS LIKE ‘_evenport’;
will match the string ‘Devenport’. It will not however match the string ‘North Devenport_555’ because ‘North Devenport’ contains more than a single character preceeding the ‘evenport’.
Case – Case Insensitivity With The UPPER Function
Now both of the queries we’ve seen so far are actually case sensitive. We can see that looking at the first one again. The first query :
SELECT
*
FROM
A
WHERE
A.suburb IS LIKE ‘%STEPHENS’;
matches ‘PORT STEPHENS’ and ‘OLD STEPHENS’ but it doesn’t actually match ‘OLD Stephens’ where there are some lower case characters in the ‘Stephens’.
We can get around that by using the function UPPER :
SELECT
*
FROM
A
WHERE
UPPER(A.suburb) IS LIKE ‘%STEPHENS’;
The function UPPER converts a string or converts a character value to upper case. So when we run that query, I’ll just have to click away from the cell to get out of edit mode before I can run the query.
When I run the query It matches both ‘PORT STEPHENS’ and ‘OLD STEPHENS’ but also ‘OLD Stephens’ where there are lower case characters included.
Escaping – Escaping Special Characters
Now both of the special characters I’ve mentioned that we can use with the LIKE operater, the percent symbol and the underscore symbol may occur as literal values in your string, you may be interested in an underscore or a percent not as a special wildcard symbol but just as that character itself. We can achieve this by using an escape character. Here I’ve got a SQL statement :
SELECT
*
FROM
A
WHERE
A.suburb IS LIKE ‘%port\_55’ ESCAPE ‘\’;
What i’m saying there is that I wish to use the backslash character as my escape character. So when it appears in my string before the underscore character it means “don’t treat this underscore character as a special wildcard character, instead just treat it as a literal underscore”.
So when I run that query. Remembering to click away to get out of Excel edit mode again. When I run that query I find it matches the string “North Devonport_55”. That underscore is appearing in my lIKE as simply an underscore character and not a wildcard.
More Functions ? – What Other Functions Are Available ?
Now, there are a number of other functions that you can use in your SQL queries in QueryCell as well as the UPPER function that we’ve seen. And you can find a list of those functions by looking at the “SQL Functions” menu option. That will open a help document listing the functions that are available.
So the LIKE operator and the UPPER SQL function don’t provide all the power of regular expressions but go somewhat in the direction of allowing us to do pattern matching and that sort of thing in our SQL.
So I hope that’s been useful and thanks for your time.