Monday, October 5, 2009

Regex within PostgreSQL

Regex (Regular expressions) are very useful. I had to make a SQL query to return only the firstname from a table where the field provides the full name.
ideia_onde=# SELECT name FROM users;
name
--------------------------------
Linus Torvalds
Randal Schwartz
Steve Wozniak
(3 rows)

ideia_onde=#
Now, with a simple substring using a regular expression:
SELECT SUBSTRING(name, '(.*?(\\s))') FROM users;
substring
-----------
Linus
Randal
Steve
(3 rows)

ideia_onde=#
What do I did?
The substring function extracts from the name field everything (.*) until (?) a space character (\\s).

1 comment:

  1. Hey, I'm in good company there. thanks!

    And I've met both of the other guys! In fact, been on cruises with both of them, twice!

    ReplyDelete