Thread: Escaping special characters

About the string "Smith \& Jones".

According to the documentation,

INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');

must work.  But it doesn't.  So, double the enclosed quotes:

INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');

Doesn't.

It works fine, but with a warning, as

INSERT INTO thing (name) VALUES ('Smith \\& Jones');

But it mightn't if I upgrade from 8.2.3.  Deprecated.  Can't risk it.
So 40,000 years from now I'll be on 8.2.3.

Granted, I'm not very bright.  Would appreciate your help.

--Elle

I could be wrong, but shouldn't it be:

INSERT INTO thing (name) VALUES ('Smith E'\\& Jones');

I'm not sure why you're including an extra single or double-quote in the string.

Regards

Thom

Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work.  But it doesn't.

I think you'll find the documentation says to use:
    SELECT E'Smith \\& Jones';

Note that the "E" precedes the quoted string, it isn't embedded in it.
If there's an example in the docs that looks like yours, that's a bug.

> But it mightn't if I upgrade from 8.2.3.  Deprecated.  Can't risk it.
> So 40,000 years from now I'll be on 8.2.3.

Doubtful - you're missing 9 releases of bugfixes already. Probably find
all your data gets eaten by a bug long before then. Read the release
notes for 8.2.x and upgrade to 8.2.<latest> at your earliest convenience.

--
  Richard Huxton
  Archonet Ltd

On Tue, Mar 17, 2009 at 10:35:20PM +1030, Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work.  But it doesn't.

You're putting things in the wrong places!  The "E" says that the
following literal is using C style escaping.  I.e. you want to say:

  E'Smith \\& Jones'

Hope that helps!

--
  Sam  http://samason.me.uk/

Sorry, (damn copy & paste).  I meant:

INSERT INTO thing (name) VALUES (E'Smith \\& Jones');

Thom

    Neanderthelle Jones wrote:

> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work.  But it doesn't.  So, double the enclosed quotes:
>
> INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');

The E can't be inside the string, it must appear before the quote
starting the string.

But first, you need to choose a setting for
standard_conforming_strings, especially if you're concerned with
compatibility against future versions. Either your session has
standard_conforming_strings set to ON or set to OFF. This is what
defines which characters have to be quoted and how.

if OFF you must escape the backslash:
test=> set standard_conforming_strings=off;
SET
test=> select E'Smith \\& Jones';
    ?column?
----------------
 Smith \& Jones
(1 row)

if ON you don't:
test=> set standard_conforming_strings=on;
SET
test=> select 'Smith \& Jones';
    ?column?
----------------
 Smith \& Jones
(1 row)

ON is supposed to become the default at some point in the future.

Cordialement,
--
 Daniel

On Tue, 17 Mar 2009, Sam Mason wrote:

> You're putting things in the wrong places!  The "E" says that the
> following literal is using C style escaping.  I.e. you want to say:
>
>   E'Smith \\& Jones'

Thanks.  Now I understand.

Elle.