Trent Richardson

Many may not know(I just recently learned) about using regular expressions within SQL statements. Instead of using substring functions and such, we can create a clean little regular expression(Oracle):

SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')

Keep in mind however that just like SQL syntax varies for each database(MySQL, Oracle, Postgres, etc.) so does the regular expression techniques. The following are a couple links to help you get started.

• MySQL Example
• Oracle Example
• MS SQL Server Example
• PostgreSQL Example

Unfortunately a little research shows SQLite does not natively support this. It sounds like dangerous waters to begin with, but if you know you won’t be switching database engines often the benefits can be rewarding!

Related posts:

1. Exploring Various SQL RegEx Syntax
2. What Happens with MySQL? VirtualBox? Java?
3. Regular Expressions with XRegExp
4. Incorporating Regex into ORM’s and Database Abstractions
5. Javascript JSON Parsing with SQL