Select Substring Using RegEx in PostgreSQL

By trent on 24 Feb in Database

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!