pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"'" in SQL INSERT statement
7+ messages / 7 participants
[nested] [flat]
* "'" in SQL INSERT statement
@ 2001-01-25 10:12 Markus Wagner <wagner@imsd.uni-mainz.de>
0 siblings, 5 replies; 7+ messages in thread
From: Markus Wagner @ 2001-01-25 10:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have some data that I wish to transfer into a database using perl/DBI.
Some of the data are strings containing the apostrophe "'" which I use
as string delimiter.
How can I put these into my database using the INSERT statement?
Thanks,
Markus
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 10:19 Oliver Elphick <olly@lfix.co.uk>
parent: Markus Wagner <wagner@imsd.uni-mainz.de>
4 siblings, 0 replies; 7+ messages in thread
From: Oliver Elphick @ 2001-01-25 10:19 UTC (permalink / raw)
To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql
Markus Wagner wrote:
>Hi,
>
>I have some data that I wish to transfer into a database using perl/DBI.
>Some of the data are strings containing the apostrophe "'" which I use
>as string delimiter.
>
>How can I put these into my database using the INSERT statement?
Escape the apostrophe with another apostrophe or a backslash:
junk=# insert into a (b) values ('John''s text');
INSERT 6815936 1
junk=# select * from a;
a | b
---+-------------
1 | some text
2 | John's text
(2 rows)
junk=# insert into a (b) values ('Fred\'s text');
INSERT 6815937 1
junk=# select * from a;
a | b
---+-------------
1 | some text
2 | John's text
3 | Fred's text
(3 rows)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"My little children, let us not love in word, neither
in tongue; but in deed and in truth."
I John 3:18
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 10:30 Alessio Bragadini <alessio@albourne.com>
parent: Markus Wagner <wagner@imsd.uni-mainz.de>
4 siblings, 1 reply; 7+ messages in thread
From: Alessio Bragadini @ 2001-01-25 10:30 UTC (permalink / raw)
To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql
Markus Wagner wrote:
> I have some data that I wish to transfer into a database using perl/DBI.
If you use Perl DBI you should issue statements like
$dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
undef, $value1, $value2);
This binding takes care of quoting and escapes all characters that may
cause problems in the database backed (e.g. "that's" becomes "that''s"
etc.)
There is a DBI mailing list where you can find more info and support:
see <http://www.isc.org/dbi-lists.html;
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 10:49 Brett W. McCoy <bmccoy@chapelperilous.net>
parent: Markus Wagner <wagner@imsd.uni-mainz.de>
4 siblings, 0 replies; 7+ messages in thread
From: Brett W. McCoy @ 2001-01-25 10:49 UTC (permalink / raw)
To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql
On Thu, 25 Jan 2001, Markus Wagner wrote:
> I have some data that I wish to transfer into a database using perl/DBI.
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?
You will need to escape them with the \ character. So "Bill's Garage"
will become "Bill\'s Garage".
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Romeo wasn't bilked in a day.
-- Walt Kelly, "Ten Ever-Lovin' Blue-Eyed Years With Pogo"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 17:42 Albert REINER <areiner@tph.tuwien.ac.at>
parent: Markus Wagner <wagner@imsd.uni-mainz.de>
4 siblings, 0 replies; 7+ messages in thread
From: Albert REINER @ 2001-01-25 17:42 UTC (permalink / raw)
To: pgsql-sql
Saluton!
On Thu, Jan 25, 2001 at 11:12:34AM +0100, Markus Wagner wrote:
...
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?
I always use this sub:
#
# This sub adapted from sub TEXT of mmusic by ChLorenz@csi.com, using
# advice from the pgsql-novice mailing list (David Rugge, 1 Aug 1999).
#
# Do the necessary quoting for strings.
#
sub stdstr {
my $or = $_[0];
return undef unless (defined($or));
$or =~ s /\\/\\\\/g; # mmusic doesn't have this, nor does D. Rugge.
$or =~ s /\'/\\\'/g;
$or =~ s /\"/\\\"/g;
$or =~ s /%/\\%/g; # mmusic doesn't have this.
return $or;
}
Obviously you also need to escape the \. I no longer remember why "
and % are needed, but I certainly did have some reason then.
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 18:03 Dan Lyke <danlyke@flutterby.com>
parent: Alessio Bragadini <alessio@albourne.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dan Lyke @ 2001-01-25 18:03 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: Markus Wagner <wagner@imsd.uni-mainz.de>; pgsql-sql
Alessio Bragadini writes:
> Markus Wagner wrote:
> > I have some data that I wish to transfer into a database using perl/DBI.
>
> If you use Perl DBI you should issue statements like
> $dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
> undef, $value1, $value2);
$dbh->quote() also puts in the appropriate escapes, ala:
$dbh->do('INSERT INTO TABLE (field) VALUES ('.$dbh->quote($value).')');
Dan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "'" in SQL INSERT statement
@ 2001-01-25 18:31 Jie Liang <jliang@ipinc.com>
parent: Markus Wagner <wagner@imsd.uni-mainz.de>
4 siblings, 0 replies; 7+ messages in thread
From: Jie Liang @ 2001-01-25 18:31 UTC (permalink / raw)
To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql
Hi,
Using a backslash to escape it.
insert into table(field) values('what\'s that');
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
On Thu, 25 Jan 2001, Markus Wagner wrote:
> Hi,
>
> I have some data that I wish to transfer into a database using perl/DBI.
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?
>
> Thanks,
>
> Markus
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-01-25 18:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
2001-01-25 10:19 ` Oliver Elphick <olly@lfix.co.uk>
2001-01-25 10:30 ` Alessio Bragadini <alessio@albourne.com>
2001-01-25 18:03 ` Dan Lyke <danlyke@flutterby.com>
2001-01-25 10:49 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2001-01-25 17:42 ` Albert REINER <areiner@tph.tuwien.ac.at>
2001-01-25 18:31 ` Jie Liang <jliang@ipinc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox