[GENERAL] UPDATE table: Syntax to Remove Terminal '\n'
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, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE table: Syntax to Remove Terminal '\n'
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) Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE table: Syntax to Remove Terminal '\n' [RESOLVED]
On Wed, 27 Aug 2014, Jeff Ross wrote: You want the E in front of the entire string, not just before the \n. select 'Use Discover: ' || E'\t' || 'Yes' || E'\n' Jeff, That did the trick. Turns out that 202 of 204 rows had the newline! The syntax that worked: update benthos set stream = 'StarvationCrk' where stream = E'StarvationCrk\n'; Much appreciated, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
