Take the 2-minute tour ×

I have a table with a string field called info. This field has some information about drivers and cars like 'Driver: Matt Car: Mustang'.

Is there a way to get the query in two columns, like that: | Matt | Mustang |.

I have no idea what I can do, maybe regular expressions?

share|improve this question
Are 'Driver' and 'Car' fixed? I mean, do you have exactly these two in every row? –  dezso Aug 8 '12 at 16:35
No they don't. @dezso –  Irish Wolf Aug 9 '12 at 12:50

5 Answers 5

up vote 1 down vote accepted
WITH tbl(id, info) AS (
    (1::int, 'Driver: Matt Car: Mustang'::text)
   ,(2,      'Driver: Billy Car: Porsche')
      ,split_part(info, ' ', 2) AS driver
      ,split_part(info, ' ', 4) As car
FROM   tbl;


 id | driver  | car
  1 | Matt    | Mustang
  2 | Billy   | Porsche

Doesn't really matter whether what strings you got in place of "Driver" and "Car" as long as the space is there and the name is to the right of the space.

share|improve this answer

If I did not understand wrong, you could maybe use string_to_array function to split the string into array elements and then reach the elements on your desire.

Please see this post for further information: Get Nth element of an array that returns from "string_to_array()" function

share|improve this answer
Would work and a more beautiful than I did, but my field can return information more or less. So much easier if I could create separate fields for each value :/ @iso_9001_ –  Irish Wolf Aug 9 '12 at 12:47

Your best bet would be to write a function to take one of these values and return a record with the appropriate columns based on what it finds. That will allow you to debug your function using SELECT statements before you go to populate the new columns (or tables). Then you can use the query in an INSERT/SELECT or UPDATE ... FROM.

You could use the plpgsql language for the functions, with the PostgreSQL regex string functions; but if you're fluent in perl or python you might want to write your function in one of those languages.

share|improve this answer

What you're talking about is a very unfortunate design pattern called serialization: that is, condensing multiple semantically different pieces of data into a single string. It's a bad idea most of the time, and in virtually all cases it's a better idea to separate your data into multiple fields, or even multiple tables. By using serialized fields, you'll find you:

  • Have much, MUCH slower queries when querying multiple, serialized subfields
  • Complicated queries for updating multiple parts of the same field
  • Feelings of depression and despair

If I had to deal with a database like that, and I couldn't alter the database structure, I'd probably end up doing a lot of the heavy lifting in program code. Many languages have better (or at least, more intuitive) string handling tools than SQL, and your performance benefits from using a RDBMS are going to be marginal at best when dealing with serialization anyway.

But if you absolutely must do this in SQL, you should read through Postgres's string handling, located here: http://www.postgresql.org/docs/9.1/static/functions-string.html

And you're right, your solution is probably going to involve a regex. Exactly what that regex will look like depends on how many serialized fields it has to parse, and exactly how it's delimited. In your example, it looks like each subfield is delimited by a pair of spaces and a pipe character between them, and if that's the case make sure that you either create rules to let you escape that delimiter as needed, or make sure that the frontend never passes the delimeter to the database.

Of course, if you have that kind of control over the frontend app, you could give the serialized data its own fields.

share|improve this answer
Okay, but my quest is find a way to do that with SQL. @sudowned –  Irish Wolf Aug 8 '12 at 15:26
Then Postgres' string handling functions are where you should be looking: postgresql.org/docs/9.1/static/functions-string.html –  sudowned Aug 8 '12 at 20:07

i did this:

select split_part(( case when
strpos(l.info,'Driver:') >= 1 then substr(l.info,strpos(l.info,'Driver:')+10, strpos(l.info,'Driver:')+9) end),E'\n',1) as driver from garage

Not at all pretty, but returns the data according to my need

share|improve this answer

Your Answer


