0

For the part where it says "b&d jigsaw" The &D keeps disappearing after running the script. When I run it, I get prompted to add a value for D. I've tried adding a quote in between the & and d, nothing works.. Anyone have any idea? Below the script is the error. Thank you!

INSERT INTO PRODUCT VALUES ('2232/QTY', 'B&D jigsaw, 12-in. blade', '30-Dec-11', 8, 5, '109.92', '0.05', 24288);
INSERT INTO PRODUCT VALUES ('2232/QWE', 'B&D jigsaw, 8-in. blade', '24-Dec-11', 6, 5, '99.87', '0.05', 24288);
INSERT INTO PRODUCT VALUES ('2238/QPD', 'B&D cordless drill, 1/2-in.', '20-Jan-12', 12, 5, '38.95', '0.05', 25595);

1 row inserted.

old:

INSERT INTO PRODUCT VALUES ('2232/QTY', 'B&D jigsaw, 12-in. blade', '30-Dec-11', 8, 5, '109.92', '0.05', 24288)

new:

INSERT INTO PRODUCT VALUES ('2232/QTY', 'B jigsaw, 12-in. blade', '30-Dec-11', 8, 5, '109.92', '0.05', 24288)

1 row inserted.

old:

INSERT INTO PRODUCT VALUES ('2232/QWE', 'B&D jigsaw, 8-in. blade', '24-Dec-11', 6, 5, '99.87', '0.05', 24288)

new:

INSERT INTO PRODUCT VALUES ('2232/QWE', 'B jigsaw, 8-in. blade', '24-Dec-11', 6, 5, '99.87', '0.05', 24288)

1 row inserted.

old:

INSERT INTO PRODUCT VALUES ('2238/QPD', 'B&D cordless drill, 1/2-in.', '20-Jan-12', 12, 5, '38.95', '0.05', 25595)

new:

INSERT INTO PRODUCT VALUES ('2238/QPD', 'B cordless drill, 1/2-in.', '20-Jan-12', 12, 5, '38.95', '0.05', 25595)
Regolith
  • 2,944
  • 9
  • 33
  • 50
cvelasquez1121
  • 35
  • 1
  • 5
  • 1
    Does [this](https://stackoverflow.com/questions/152837/how-to-insert-a-string-which-contains-an) help? – CodingYoshi Jul 14 '17 at 03:53
  • I read through it twice but am not understanding the SET DEFINE and SELECT stuff. I tried the ||'&'|| that was in the other post, and no good. I'm a beginner in SQL and i've been struggling. – cvelasquez1121 Jul 14 '17 at 04:02

1 Answers1

1

By default, SQL Plus treats '&' as a special character that begins a substitution string. This can cause problems when running scripts that happen to include '&' for other reasons:

If you know your script includes (or may include) data containing '&' characters, and you do not want the substitution behaviour as above, then use set define off to switch off the behaviour while running the script:

SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd

You might want to add set define on at the end of the script to restore the default behaviour.

Anil
  • 595
  • 1
  • 5
  • 15