Special Characters! 2

[drkestrel]

I have a PL/SQL procedure that takes a parameter

insertVal

. This value is inserted into a DB field as a new row.
Unfortunately, if the user pass in

insertVal

which contains the special character ', it would fail!
What do I do? I tried "'", but that didn't work either!

 

[joezapp]

For just a single quote I think it's '''' (four single quotes) - the first delimits the start of the string, the next two identify the single character in the string and the fourth delimits the end of the string. A string such as "joe's text string" would then be 'joes''s test string'

Hope this is of use.

Joe.

 

[MikeJones]

I'd write a fdunction that takes a string and returns it with all ' turned into ''

then wrap the insert in this function.

 

[drkestrel]

I have now got a 'simpler' question...

Is it at all allowed by Oracle's SQL to insert values containint the character ' (single quote) into a field. If so, how?
I tried
'''
"'"
\'

but none works!

 

[karluk]

I'm not sure how this question differs from your original post. The answer was given by Joe and Mike - You can embed a single quote in a character string by typing two consecutive single quotes. For example try executing

select 'Where there''s life there''s hope.' from dual;

SQL will replace '' with ' and return

Where there's life there's hope.