Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Escape-sequence in SQL

0 views
Skip to first unread message

Don HO

unread,
May 8, 2004, 7:01:15 PM5/8/04
to
Hi,

What's the symbol of escape-sequence in SQL?

INSERT INTO table (id,sentence) VALUES(NULL, concat('The \'id is ',id))

is it correct to use the symbol '\' ?
or is it better to use '' (2 single quotes) to express the character ' ?

Is there a standard definition about that?

Thanx

--
Don HO
http://notepad-plus.sourceforge.net/
a powerful free (and open source) multi-function editor

J.O. Aho

unread,
May 9, 2004, 1:02:54 AM5/9/04
to
Don HO wrote:
> Hi,
>
> What's the symbol of escape-sequence in SQL?
>
> INSERT INTO table (id,sentence) VALUES(NULL, concat('The \'id is ',id))
>
> is it correct to use the symbol '\' ?
> or is it better to use '' (2 single quotes) to express the character ' ?
>
> Is there a standard definition about that?

At least in MySQL (could be different in mssql) you use the '\' infront of the
character.

//Aho

Andy Hassall

unread,
May 9, 2004, 7:23:51 AM5/9/04
to
On Sun, 9 May 2004 01:01:15 +0200, "Don HO" <do...@altern.org> wrote:

>What's the symbol of escape-sequence in SQL?
>
>INSERT INTO table (id,sentence) VALUES(NULL, concat('The \'id is ',id))
>
>is it correct to use the symbol '\' ?
>or is it better to use '' (2 single quotes) to express the character ' ?

It Depends. Different databases use different rules.
For MySQL, slash. For Oracle, two single quotes.

>Is there a standard definition about that?

The draft of ANSI SQL-99 I've got has the following production for string
literals:

<general literal> ::=
<character string literal>
| <national character string literal>
| <bit string literal>
| <hex string literal>
| <datetime literal>
| <interval literal>

<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>... ]
<quote> }... ]


<introducer> ::= <underscore>

<character representation> ::=
<nonquote character>
| <quote symbol>

<nonquote character> ::= !! See the Syntax Rules.

<quote symbol> ::= <quote><quote>


Therefore the standard says two single quotes.

--
Andy Hassall <an...@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

Andy Hassall

unread,
May 9, 2004, 7:42:12 AM5/9/04
to
On Sun, 09 May 2004 12:23:51 +0100, Andy Hassall <an...@andyh.co.uk> wrote:

> The draft of ANSI SQL-99

SQL-92, even.

Don HO

unread,
May 9, 2004, 11:02:22 AM5/9/04
to
Thank you Andy.

--
Don HO
http://notepad-plus.sourceforge.net/
a powerful free (and open source) multi-function editor


"Andy Hassall" <an...@andyh.co.uk> a écrit dans le message de news:
e86s909tnp8srq52b...@4ax.com...

0 new messages