I thought of another handy scenario for using regular expressions in Postgres. This time not within the conditions, but in the returned results. Lets say for instance you have a column with URL, (maybe blog comments or something) and you would like to get a list of all the domain names, not the full paths trailing it nor the http://, how would you do that? Without regular expressions you would be forced to use a combination of various string functions to look for the domain name, and you will also aquire a headache free of charge. However with a simple regular expression you could do something like this:


select SUBSTRING(url FROM 'http://([^/]*).*') as domainname from my table

Pretty simple you must admit. Ok, so that is pretty straight forward. Now lets say we wanted to scan a column for any phone numbers. I’ll keep the phone number regular expression simple for this example. The phone number is 10 numbers, with only a possible dash after the third and sixth numbers.


select SUBSTRING(colname FROM '([0-9]{3}\-?[0-9]{3}\-?[0-9]{4})') as numstr from tablename

Ok, now one final scenario. We are still looking for a phone number, but there could be more than one per column. We would like to find them all! Well, there’s a regex for that.


select regexp_matches(colname, '([0-9]{3}\-?[0-9]{3}\-?[0-9]{4})', 'g') as numstr from tablename

Now you can see we’ve changed up our substring function to regexp_matches, which will give us the ability to search for all matches. I’m sure most everyone is familar with a regular expression matching function so this is nothing new. You can find more information on using these functions in the Postgres Docs. Hope you’ve enjoyed more Regular Expressions with Postgres!

Be Sociable, Share!

Related posts:

  1. Incorporating Regex into ORM’s and Database Abstractions
  2. CakePHP, PostgreSQL, and Regex
  3. Adding PostgreSQL Regex Support in CakePHP
  4. Exploring Various SQL RegEx Syntax
  5. Are You Using Regular Expressions Within SQL?