Re: UPDATE table: Syntax to Remove Terminal '\n'

On 14/08/28 8:04, Ian Barwick wrote:
> On 14/08/28 7:31, Rich Shepard wrote:
>> I have some rows in a table where a column attribute has a newline (\n)
>> appended to the string. How do I represent that newline character in a SQL
>> statement using psql?
>>
>> I've tried adding E'\n' to the end of the string but that doesn't work.
>>
>> Here's what I see when I select distinct for that column:
>>
>> StarvationCrk+
>>
>>
>> That's a blank line below the name.
>>
>> TIA,
>
> Not sure what you mean by "doesn't work"; if you want a more precise
> rendering of the newline character, the unicode linestyle (suggested
> by Tom Lane in the previous thread) should do the trick:
>
> postgres=> \pset linestyle unicode
> Line style (linestyle) is unicode.
> postgres=> SELECT E'foo\n';
> ?column?
> ──────────
> foo ↵
>
> (1 row)

And to remove the newline character you can do something like this:

postgres=> SELECT regexp_replace(E'foo\n', E'\n$','');
regexp_replace
────────────────
foo
(1 row)

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services