[sql] How to insert a string which contains an "&"

How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database.

I'm not sure if it makes a difference, but I'm using Oracle 9i.

SET ESCAPE ON;
INSERT VALUES("J\&J Construction") INTO custnames;

(Untested, don't have an Oracle box at hand and it has been a while)

If you are using sql plus then I think that you need to issue the command

SET SCAN OFF

INSERT INTO TEST_TABLE VALUES('Jonhy''s Sport &'||' Fitness')

This query's output : Jonhy's Sport & Fitness

The correct syntax is

set def off;
insert into tablename values( 'J&J');

I've found that using either of the following options works:

SET DEF OFF

or

SET SCAN OFF

I don't know enough about databases to know if one is better or "more right" than the other. Also, if there's something better than either of these, please let me know.

If you are doing it from SQLPLUS use

SET DEFINE OFF  

to stop it treading & as a special case

In a program, always use a parameterized query. It avoids SQL Injection attacks as well as any other characters that are special to the SQL parser.

You can insert such an string as 'J'||'&'||'Construction'. It works fine.

insert into table_name (col_name) values('J'||'&'||'Construction');

SET SCAN OFF is obsolete http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apc.htm

There's always the chr() function, which converts an ascii code to string.

ie. something like: INSERT INTO table VALUES ( CONCAT( 'J', CHR(38), 'J' ) )

An alternate solution, use concatenation and the chr function:

select 'J' || chr(38) || 'J Construction' from dual;

Look, Andrew:

"J&J Construction":

SELECT CONCAT('J', CONCAT(CHR(38), 'J Construction')) FROM DUAL;

There's always the chr() function, which converts an ascii code to string.

ie. something like: INSERT INTO table VALUES ( CONCAT( 'J', CHR(38), 'J' ) )

Stop using SQL/Plus, I highly recommend PL/SQL Developer it's much more than an SQL tool.

p.s. Some people prefer TOAD.

Stop using SQL/Plus, I highly recommend PL/SQL Developer it's much more than an SQL tool.

p.s. Some people prefer TOAD.