how to insert ' or " in table
Dear All
I have created a 'test' table and then try to insert the values.
create table 'informix'.test (
it_name VARCHAR(20)
)
insert into test(it_name)values(' pipe 2' 2" ');
it gives the sql syntax error .
I have also used the string escape character('\\\\') but nothing happened .
the inserted value is depend upon user it can be 'pipe 2' 2" ' or 'pipe 2'
2' ' or 'pipe 2" 2" ' or 'pipe 2' ' or 'pipe 2" '.
thanks in advance for any help.
Regards-
Nierjesh
--001517576466945f780473496444
If the insert will be processed by a host language, use a replaceable
parameter and put the string into a host variable.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Sep 11, 2009 at 4:49 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote:
> Dear All
>
> I have created a 'test' table and then try to insert the values.
>
> create table 'informix'.test (
>
> it_name VARCHAR(20)
> )
>
> insert into test(it_name)values(' pipe 2' 2" ');>
> it gives the sql syntax error .
>
> I have also used the string escape character('\\\\') but nothing happened .
>
> the inserted value is depend upon user it can be 'pipe 2' 2" ' or 'pipe 2'
> 2' ' or 'pipe 2" 2" ' or 'pipe 2' ' or 'pipe 2" '.
>
> thanks in advance for any help.
>
> Regards-
> Nierjesh
>
> --001517576466945f780473496444
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747ba9c5d7f5204734a76a3
On Fri, Sep 11, 2009 at 01:49, nierjesh kumar <nierjeshkumar@gmail.com> wrote:
> I have created a 'test' table and then try to insert the values.
>
> create table 'informix'.test (
> it_name VARCHAR(20)
> )
>
> insert into test(it_name)values(' pipe 2' 2" ');>
> it gives the sql syntax error .
>
> I have also used the string escape character('\\\\') but nothing happened .
Standard SQL requires that you use a pair of the opening quote
character in the string to embed a single copy of that into the table:
INSERT INTO test(it_name) VALUES(' pipe 2'' 2" '); -- 1
INSERT INTO test(it_name) VALUES(" pipe 2' 2"" "); -- 2
INSERT INTO test(it_name) VALUES("He said, ""Don't do it!"""); -- 3
INSERT INTO test(it_name) VALUES('He said, "Don''t do it!"'); -- 4
When I look at that in variable-width font, it is hard to tell what's what,
but:
(1) has a single quote, a pair of single quotes, a double quote, and a
closing single quote.
(2) has a double quote, a single quote, a pair of double quotes, and a
closing double quote.
(3) has a double quote, a pair of double quotes, a single quote, a
pair of double quotes, and a closing double quote.
(4) has a single quote, a double quote, a pair of single quotes, a
double quote, and a closing single quote.
Strict SQL according to the standard using single quotes around
strings and double quotes around delimited identifiers. IDS will play
like that if you set DELIMIDENT in the environment.
> the inserted value is depend upon user it can be 'pipe 2' 2" ' or 'pipe 2'
> 2' ' or 'pipe 2" 2" ' or 'pipe 2' ' or 'pipe 2" '.
As Art Kagel suggested, if you're working in a programming language
that can support placeholders, use them instead of quoting the string.
If you must quote a string for embedding into an SQL statement, do so
carefully - preferably using a function for the job.
I4GL has slightly different rules - it recognizes backslash-quote
(single or double) and converts that to doubled-up quote.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Ogden Nash - "The trouble with a kitten is that when it grows up,
it's always a cat." -
http://www.brainyquote.com/quotes/authors/o/ogden_nash.html