In one of the PostgreSQL systems we work with often, there is only one street address field. To get around the need for a change to the table structure, our IT folks simply use a new line character (\n) to denote that there is a second line of address information. The problem becomes, when we go to generate a mailing list, how can we export a list to send to the mailhouse with all address information on one line?
The answer is to use the SPLIT_PART function. See the example below:
select split_part(street,'\n',1) as address1, split_part(street,'\n',2) as address2 from members
Here’s the step by step:
- Select whatever field you need to split (or you can enter a text string as the first parameter of the split_part)
- The second parameter indicates what to split by. In this case, we are using the new line character, \n. This could also be used if you have imported a delimited field into one value, such as a |, in which case you would put that between the quotes (eg. ‘|’).
- Indicate which section of the split field you want to keep in the third parameter. Since we only have two sections in this example, one for each line of a two-line street address, we first take 1 for address1 and then 2 for address2.
Voila! You now have each piece of the address in a separate field and can pass them into an exportable, delimited file without trouble.
- When using a delimiter other than a new line break or similar control character, be wary of what’s in your values. This is especially true of space- or comma-delimited fields as they are most prone to having the same character used in pieces of text that may be the section that you want to keep. In these cases, you would need to pull sections apart based on a combination of factors, usually double-quotes and the delimiter, in order to properly separate them.
- Also be sure to know how many delimiters there are in your field or text. It may sound silly, but re-working this to get the right part or finding that you’re missing a third line of information gets real old real quick.
On a funny aside, while trying to find an appropriate image to go with this post, I stumbled upon images of Slash, guitarist for Guns N’ Roses, whom I totally adore.
2 thoughts on “Split Field by Line Break in PostgreSQL”
You need to add an “E”
select split_part(street,E’\n’,1) as address1, split_part(street,E’\n’,2) as address2