This is part two of my quest to Incorporating Regex into ORM’s and Database Abstractions. My first steps to exploring the option to implement uniform regex support into my own database orm/abstraction is to compare the syntax of the more popular database systems. To do so I will build an sql query where possible to search the body of page contents for ‘bla bla bla..’ in a test database table.
select * from pages where body REGEXP '(bla(\s)?)+'
select * from pages where body ~ '(bla(\s)?)+'
SQL Server doesn’t appear to natively support regex without the help of CLR.
select * from pages where dbo.RegExMatch( body,'(bla(\s)?)+')
SQLite similarly does not natively support Regular Expressions, however SQLite can be extended as well to do so:
The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named “regexp” is added at run-time, that function will be called in order to implement the REGEXP operator.
So SQLite recognizes REGEXP, but it is not implemented. It states that if we implement regexp() it can use it. So here might be the way to implement REGEXP using PHP. Implementing this should result in a syntax similar to MySQL’s syntax.
DB2 falls into the same category as SQL Server and SQLite by needing to create a function to implement Regular Expressions. Implementing this function can be done in C and appears to support pretty standard RegEx syntax.
Oracle Database does reportedly support Regular Expressions. While I do not have an Oracle database to test on, it is reported to work like the following:
SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
Informix seems to support regular expressions, however it does require that you have regexp1.0 installed to use them. Using this feature consists of calling a function rather than using an operator.
I could not find any definite resources to indicate whether Sybase supports regular expressions or not. Perhaps if someone is familiar with this database they could shed some light on this.
After examining the different database engines a little more closely and their capabilities of handling Regular Expressions, I have found that support is surprisingly not very good from proprietary databases, however there are ways to implement it in most systems. However MySQL, Postgres, and Oracle(the exception to the proprietary databases) appear to support regex straight out of the box. This is a little discouraging, however if you could supply the necessary tools, libraries, functions, or procedures to enable regular expressions it would still be possible.