Friday, January 16, 2009

How to insert ampercent(&) into a table?

Today I had to insert few thousands rows from insert script using sqlplus tool. Script stopped on a first row that contained ampercent(&) like 'P & G'.

SqlPlus was expecting substitution variable.

Little demo case:
msutic@TESTDB> create table test (text varchar2(100));

Table created.


msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips');
Enter value for videoclips:

msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips');
Enter value for videoclips:

1 row created.

msutic@TESTDB> select * from test;

TEXT
-------------------------------------------
Pips,Chips

1 row selected.



So how to insert "&" into table. Here are some solutions:

msutic@TESTDB> set define off
msutic@TESTDB>
msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips');

1 row created.

msutic@TESTDB> select * from test;

TEXT
----------------------------------------------------
Pips,Chips & Videoclips

1 row selected.

msutic@TESTDB> set define on


msutic@TESTDB> set escape \
msutic@TESTDB> insert into test values ('Pips,Chips \& Videoclips');

1 row created.

msutic@TESTDB> select * from test;

TEXT
--------------------------------------------------
Pips,Chips & Videoclips

1 row selected.

msutic@TESTDB> set escape off


msutic@TESTDB> insert into test values ('Pips,Chips '|| '&' ||' Videoclips');

1 row created.

msutic@TESTDB> select * from test;

TEXT
--------------------------------------------------
Pips,Chips & Videoclips

1 row selected.


msutic@TESTDB> insert into test values ('Pips,Chips '|| chr(38) ||' Videoclips');

1 row created.

msutic@TESTDB> select * from test;

TEXT
------------------------------------------------
Pips,Chips & Videoclips

3 comments:

  1. It can be inserted by applying following code :

    insert into demo values('r'||chr(38)||'d');.but this is now working for generation of insert statements through procedure.

    ReplyDelete
  2. also you can use
    SQL> set define off
    SQL> insert into test values ('Pips,Chips & Videoclips');
    SQL> COMMIT;

    to solve this issue

    Ahmed Abd El Latif
    airtifa23@gmail.com

    ReplyDelete
  3. Hi Ahmed,

    check my second example.
    I've noted this solution already but thanks for commenting ;)

    Regards,
    Marko

    ReplyDelete