Regular Expressions In SQL
Regular expressions are pretty easy to use, however, different DBMS’s handle them differently.
In PostgreSQL, regular expression searches are done via the “~” character. For instance:
SELECT * FROM table1 WHERE column1 ~ '^(.?)unq([a-z]{4})([1-9]+)$';
In MySQL, this is done via REGEXP:
SELECT * FROM table1 WHERE column1 REGEXP '^S([c-z])n(.*)$';
In Oracle, this is done via the REGEXP_LIKE function:
SELECT * FROM table1 WHERE REGEXP_LIKE(column1, '[[:digit:]]{2}$');
June 13th, 2008 at 11:47 pm
Alas, this is one of the failings of the SQL standard. There’s the SQL:1999 SIMILAR TO, which has it’s own pattern syntax. It looks to be supported by PostgreSQL, unsupported by Oracla, and an alias for REGEXP/RLIKE in MySQL.
What’s worse is that the regexp syntax is almost certainly different from one DB to another.