There are a whole set of fields in the databases I’m using here that are tilde-delimited (~) varchar strings with a mess of key-value pairs, the values from which I really need. Unfortunately, since they are varying character lengths, in no particular set order within that field, it is impossible to substring your way efficiently through them. Thankfully, there is a RegEx genius on my team who produced a handy chuck of code that pgSQL can easily recognize, parse and process for pulling precisely what I need. Continue reading
text-parsing
Split Field by Line Break in PostgreSQL
Standard
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: Continue reading