Including '&' in INSERT SQL Statement

[spook007]

I'm trying to run the following query:

INSERT INTO SUBJECT VALUES ('ARTS & HUMANITIES','AH');

When I try to run it it asks me to enter a value for humanities. I suppose that the '&' sign before humanities makes it think that humanities is a variable. Is there any work around for this so that you can include '&' in the SQL statement. Thanks.

 

[vijaychin]

INSERT INTO SUBJECT VALUES ('ARTS ' || chr(38) || ' HUMANITIES','AH');

 

[karluk]

There are at least two work arounds that I've seen. In SQL*Plus you can set an escape character, and then precede the & with the escape character. That will cause SQL*Plus to ignore the special meaning of &.

set escape INSERT INTO SUBJECT VALUES ('ARTS \& HUMANITIES','AH');
set escape off

The other work around is to take use the ascii equivalent of &, which is chr(38). You can build your string by concatenating the part before the &, then chr(38), then the part after the &.

INSERT INTO SUBJECT VALUES ('ARTS '||chr(38)||' HUMANITIES','AH');

 

[spook007]

I've got a field that will store a brief description of an article. Approx 500 characters. VARCHAR(500)

The authors will write their description of the article on a web form interface. From the explanations I've received, I understand that everytime the author submits the form, I will have to write a script that will search through every character and add a '\' before every '&' in order for my INSERT SQL statement to work in Oracle?

Not that I'm lazy... and don't want to write the script... Just wondering if anyone has an alternative that will run more efficient. Thanks

 

[shoawe]

set scan off will do what you intend to.

 

[sem]

The problem with & is more sql*plus then Oracle specific. Maybe sql*plus is not the best tool to insert data from the web, because it has too many extra features. But in any case it fortunately also has the means to suppress them

 

[Bimalaggarwal]

Hi,

If you are on SQL*PLUS, then
SET DEF OFF
will serve your purpose.

 

[spook007]

I did notice that in SQL*PLUS it needs me to \& but when I run my ASP script, it works just fine, so Sem was correct, it seems to be a SQL*PLUS thing only...

Now I'm trying to find a way in which I can enter single quotes '. I've read the documentation and it mentions that in order to display single quotes with in the INSERT string, you have to have enter two single quotes. For example the following SQL will error out when I run it from my ASP script.

INSERT INTO ARTICLE VALUES ('Johnson's & Johnson's');

It wants me to enter it like:

INSERT INTO ARTICLE VALUES ('Johnson''s & Johnsons''s');

This is easy to enter when you have a short phrase... but what about when I have authors typing in an article description that is about 255 characters? Do I have to put together a script to detect single quotes ' and when it finds them to add another single quote? Is there an alternative way to enter strings. For example...

INSERT INTO ARTICLE VALUES ("Johnson's & Johnson's"

I know the above doesn't work, but it would solve the problem with the single quotes. I know that MySQL uses the above format, perhaps someone knows of another syntax that I haven't read about... any suggestions welcomed! Thanks.

 

[karluk]

I'm glad your problem with & was more apparent than real. Unfortunately you aren't getting so lucky with quotes.

I don't think there's any solution to this problem without writing a function to replace every single quote with two consecutive quotes. A search of the ASP forum turns up several references to this issue including thread333-78046.

It seems to me that this issue is more an ASP question than Oracle. In Oracle the replace is easy:

select replace(string_col,'''','''''') from your_table;

But you want to do the replace in ASP before you ever get to Oracle. With luck ASP has a similar simple replace function. Perhaps you should pose this question in the ASP forum to get expert advice.

 

[spook007]

Everyone here has been awsome... I appreciate your input and advice... Karluk, thanks for the reference to the other thread... it helped out.