dinsdag 8 april 2014

Why I like PostgreSQL more than MySQL: regular expressions in a string-replace.

MySQL's regexp functions are limited to matching patterns, PostgreSQL can also use them to fetch substrings, and to replace substrings.

For example; to remove any spaces from a dutch zipcode:

SELECT regexp_replace('   134 5 AA   ', E'\\s', '', 'g')

That is: replace any whitespace character with an empty string, globally.

This way you can write a CHECK constraint, trigger or rule that accepts a zipcode in the raw format, and validate and clean it before storing it, making life a little easier on the application.