SQL Insert question

[Raven]

Let's say I have a text field and want to insert into that field something with an apostrophe. For example, If I wanted to set a text field as: A's

This is what I currently have, and it works for anything without an apostrophe:
" ' " & DivisionalSetupForm.Text1(x - 1).Text & " ', "

So the insertion looks something like this 'A's'

So it inserts A, and I get an error when it reads the s'

Any ideas?

[rjolley]

In Oracle, it would be something like this:

insert into tableA (text_column)
values ('A''s');

The ' is escaped by another ' in Oracle's SQL syntax.

[Glengoyne]

In Oracle, it would be something like this:

insert into tableA (text_column)
values ('A''s');

The ' is escaped by another ' in Oracle's SQL syntax.]


This is correct. Just in case he actually wanted the 'A's' exactly as it was typed including the outer apostrophes.



select '''A''s''' from dual;
returns --> 'A's'

That is three leading and trailing apostrophes.

I seem to remember much more complicated scenarios, where 5 consecutive apostrophes were used, but I can't piece it together now. Management has dulled my mind.

[21C]

What I use to avoid this situation is to use something like:
Replace(txtToBeInserted, "'", "''")

That way, if the text includes a single ' it gets replaced by two '' that are needed by SQL.

So in your example:
" ' " & Replace(DivisionalSetupForm.Text1(x - 1).Text,"'","''") & " ', "

[Raven]

What I use to avoid this situation is to use something like:
Replace(txtToBeInserted, "'", "''")

That way, if the text includes a single ' it gets replaced by two '' that are needed by SQL.

So in your example:
" ' " & Replace(DivisionalSetupForm.Text1(x - 1).Text,"'","''") & " ', "



Thanks, Richard. Worked perfectly

[sachmo71]

you said 'insert'

heh.