PDA

View Full Version : Understanding the LIKE and == pattern matching operators



barrymavin
2015-03-21, 02:34
In Lianja the SQL LIKE operator (or == for shorthand) can handle a wide range of pattern matching criteria.

In the next build 1.4.2 I have added support for case insensitive pattern matching by postfixing /i to the pattern and added better support for ranges of characters.



% match any of one or more characters
* match any of one or more characters
? match any one character
_ match any one character
[abc] match any of the characters within the set [...]
[a-c] match any of the characters within the set [...]
[^abc] match any of the characters not within the set [...]
[^a-c] match any of the characters not within the set [...]


Examples:



select * from shippers where companyname like "%Shippers%"
or
select * from shippers where companyname == "%Shippers%"


Alternatively ignoring case by postfixing /i.



select * from shippers where companyname like "%Shippers%/i"


Matching any one character.



select * from shippers where companyname like "%Sh?ppers%"
or
select * from shippers where companyname like "%Sh_ppers%"


Matching a range of characters in a set of characters.



select * from shippers where companyname like "%Shipp[abcde]rs%"
or
select * from shippers where companyname like "%Shipp[a-e]rs%"


Matching a range of characters not in a set of characters.



select * from shippers where companyname like "%Shipp[^abcde]rs%"
or
select * from shippers where companyname like "%Shipp[^a-e]rs%"


Interestingly you can use these in expressions that are not part of a SQL statement,



// position on a record
scan rest while companyname like "%Sh?ppers%"
// process the data
endscan


or alternate syntax.



// compare using a regular expression and ignore case
if companyname == "%Sh?pp[a-e]rs%/i"
// do something
endif


Note that pattern matching is not handled by the SQL query optimizer so be sure to include a good restriction in your WHERE condition so that a sequential scan of all your data is not performed.