Re: Inserting string data including &

Tim Slattery <Slattery_T_at_bls.gov> wa:
>I'm trying to load a database table. The table contains several string
>columns, and in many places the value of that string must contain an
>ampersand (&).

>When I code an insert statement like this:

>insert into edit_messages (edit_number, title, intro)
> values(123, '--&nbsp; line of error message', '--&nbsp; line 2 of
>message);

>SQL*Plus assumes that &nbsp is a variable and prompts me for a value
>for it. Is there a way to escape the & so that it becomes just part of
>the string?

Preface it with and follow it up with:

set def off

insert into edit_messages (edit_number, title, intro)   values(123, '--&nbsp; line of error message', '--&nbsp; line 2 of message);

set def on

(you can also change the def character, "set def `" or some other character you don't use)

Or, you can use the chr() function for amp in it:

  insert into edit_messages (edit_number, title, intro)   values(123, '--'||chr(38)||'nbsp; stuff...', '--'||chr(38)||'nbsp; more stuff');

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator