«

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.

Example 1):

it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
it''s yours, 12, 42, 2008/10/06

Example 2):

'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'

The tricky part is that the data file may or may not have the string
fields wrapped in single quotes. In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.

Does anybody have handy perl one-liner or script that tackles this?
Thanks!

Henry J. wrote:
> Need to escape single quotes ( i.e., ' -> '' ) in a data file before
> sending to DB as part of insert SQLs.
>
> Example 1):
>
> it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
> it''s yours, 12, 42, 2008/10/06
>
> Example 2):
>
> 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
> mine', 'it''s yours', 12, 42, '2008/10/06'
>
> The tricky part is that the data file may or may not have the string
> fields wrapped in single quotes. In Example 1), it is not and single
> quotes around fields will be added by another script before sending to
> DB, in Example 2), its fields are already enclosed in single quotes
> and will be sent to DB as is.
>
> Does anybody have handy perl one-liner or script that tackles this?
> Thanks!
>

$dbh->prepare("SELECT foo, bar FROM table WHERE baz=?")->execute($baz);



--
RGB

On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:

>Need to escape single quotes ( i.e., ' -> '' ) in a data file before
>sending to DB as part of insert SQLs.
>
> Example 1):
>
> it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
>it''s yours, 12, 42, 2008/10/06
>
> Example 2):
>
> 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
>mine', 'it''s yours', 12, 42, '2008/10/06'
>
>The tricky part is that the data file may or may not have the string
>fields wrapped in single quotes. In Example 1), it is not and single
>quotes around fields will be added by another script before sending to
>DB, in Example 2), its fields are already enclosed in single quotes
>and will be sent to DB as is.
>
>Does anybody have handy perl one-liner or script that tackles this?
>Thanks!


I don't think this can be a one liner, maybe.
In esscence this does it all:

sln

#############
# Delim Regex
#############

use strict;
use warnings;

my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
it's wants it's gets ";


while ($str =~ /([^,\n]+)/g)
{
my $val = $1;
$val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
$val =~ s/'/''/g;
$val = "'$val'";
print "val = $val\n";
}


__END__

output:

val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s wants it''s gets'

On Mon, 06 Oct 2008 18:25:07 GMT, wrote:

>On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <> wrote:
>
>>Need to escape single quotes ( i.e., ' -> '' ) in a data file before
>>sending to DB as part of insert SQLs.
>>
>> Example 1):
>>
>> it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
>>it''s yours, 12, 42, 2008/10/06
>>
>> Example 2):
>>
>> 'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
>>mine', 'it''s yours', 12, 42, '2008/10/06'
>>
>>The tricky part is that the data file may or may not have the string
>>fields wrapped in single quotes. In Example 1), it is not and single
>>quotes around fields will be added by another script before sending to
>>DB, in Example 2), its fields are already enclosed in single quotes
>>and will be sent to DB as is.
>>
>>Does anybody have handy perl one-liner or script that tackles this?
>>Thanks!
>
>
>I don't think this can be a one liner, maybe.
>In esscence this does it all:
>
>sln
>
>#############
># Delim Regex
>#############
>
>use strict;
>use warnings;
>
>my $str = "
>it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
>it's wants it's gets ";
>
>
>while ($str =~ /([^,\n]+)/g)
>{
> my $val = $1;
> $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;

# just for grins, reconstitute partial fixes.
# there goes that one-liner possibility

$val =~ s/'+/'/g;
#

> $val =~ s/'/''/g;
> $val = "'$val'";
> print "val = $val\n";
>}
>
>
>__END__
>
>output:
>
>val_a = 'it''s mine'
>val_a = 'it''s yours'
>val_a = '12'
>val_a = '42'
>val_a = '2008/10/06'
>val_a = 'it''s mine'
>val_a = 'it''s yours'
>val_a = '12'
>val_a = '42'
>val_a = '2008/10/06'
>val_a = 'it''s wants it''s gets'
>
>

>>>>> "HJ" == Henry J <> writes:

HJ> Need to escape single quotes ( i.e., ' -> '' ) in a data file
HJ> before sending to DB as part of insert SQLs.

Use placeholders in your SQL statement, and let the database driver
handle escaping as necessary. perldoc DBI

Charlton



--
Charlton Wilbur

On Oct 6, 2:25*pm, s...@netherlands.com wrote:
> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
> >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
> >sending to DB as part of insert SQLs.
>
> > * * * Example 1):
>
> > * * * * * it's mine, it's yours, 12, 42, 2008/10/06 * => it''s mine,
> >it''s yours, 12, 42, 2008/10/06
>
> > * * * Example 2):
>
> > * * * * * 'it's mine', 'it's yours', 12, 42, '2008/10/06' *=> 'it''s
> >mine', 'it''s yours', 12, 42, '2008/10/06'
>
> >The tricky part is that the data file may or may not have the string
> >fields wrapped in single quotes. *In Example 1), it is not and single
> >quotes around fields will be added by another script before sending to
> >DB, in Example 2), its fields are already enclosed in single quotes
> >and will be sent to DB as is.
>
> >Does anybody have handy perl one-liner or script that tackles this?
> >Thanks!
>
> I don't think this can be a one liner, maybe.
> In esscence this does it all:
>
> sln
>
> #############
> # Delim Regex
> #############
>
> use strict;
> use warnings;
>
> my $str = "
> it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12,42, '2008/10/06',
> it's wants it's gets ";
>
> while ($str =~ /([^,\n]+)/g)
> {
> * * * * my $val = $1;
> * * * * $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
> * * * * $val =~ s/'/''/g;
> * * * * $val = "'$val'";
> * * * * print "val = $val\n";
>
> }
>
> __END__
>
> output:
>
> val_a = 'it''s mine'
> val_a = 'it''s yours'
> val_a = '12'
> val_a = '42'
> val_a = '2008/10/06'
> val_a = 'it''s mine'
> val_a = 'it''s yours'
> val_a = '12'
> val_a = '42'
> val_a = '2008/10/06'
> val_a = 'it''s wants it''s gets'- Hide quoted text -
>
> - Show quoted text -

Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
cannot add or remove enclosing single quotes).

Henry J. schreef:

> Need to escape single quotes ( i.e., ' -> '' ) in a data file before
> sending to DB as part of insert SQLs.

No, you don't.

--
Affijn, Ruud

"Gewoon is een tijger."

On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <> wrote:

>On Oct 6, 2:25*pm, s...@netherlands.com wrote:
>> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
>> >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
>> >sending to DB as part of insert SQLs.
>>
>> > * * * Example 1):
>>
>> > * * * * * it's mine, it's yours, 12, 42, 2008/10/06 * => it''s mine,
>> >it''s yours, 12, 42, 2008/10/06
>>
>> > * * * Example 2):
>>
>> > * * * * * 'it's mine', 'it's yours', 12, 42, '2008/10/06' *=> 'it''s
>> >mine', 'it''s yours', 12, 42, '2008/10/06'
>>
>> >The tricky part is that the data file may or may not have the string
>> >fields wrapped in single quotes. *In Example 1), it is not and single
>> >quotes around fields will be added by another script before sending to
>> >DB, in Example 2), its fields are already enclosed in single quotes
>> >and will be sent to DB as is.
>>
>> >Does anybody have handy perl one-liner or script that tackles this?
>> >Thanks!
>>
>> I don't think this can be a one liner, maybe.
>> In esscence this does it all:
>>
>> sln
>>
>> #############
>> # Delim Regex
>> #############
>>
>> use strict;
>> use warnings;
>>
>> my $str = "
>> it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
>> it's wants it's gets ";
>>
>> while ($str =~ /([^,\n]+)/g)
>> {
>> * * * * my $val = $1;
>> * * * * $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
>> * * * * $val =~ s/'/''/g;
>> * * * * $val = "'$val'";
>> * * * * print "val = $val\n";
>>
>> }
>>
>> __END__
>>
>> output:
>>
>> val_a = 'it''s mine'
>> val_a = 'it''s yours'
>> val_a = '12'
>> val_a = '42'
>> val_a = '2008/10/06'
>> val_a = 'it''s mine'
>> val_a = 'it''s yours'
>> val_a = '12'
>> val_a = '42'
>> val_a = '2008/10/06'
>> val_a = 'it''s wants it''s gets'- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
>cannot add or remove enclosing single quotes).

Without at least removeing enclosing quotes, the inner quote would be hard to
substitute. Not impossible, but harder. Its easy to test if there is enclosing
quote (then add it back later), but it still has to be removed before doing the
inner quote substitution.

Either way it can be done. How did you do it?

Thanks!

sln

On Oct 6, 3:48*pm, s...@netherlands.com wrote:
> On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
> >On Oct 6, 2:25*pm, s...@netherlands.com wrote:
> >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
> >> >Need to escape single quotes ( i.e., ' -> '' ) in a data file before
> >> >sending to DB as part of insert SQLs.
>
> >> > * * * Example 1):
>
> >> > * * * * * it's mine, it's yours, 12, 42, 2008/10/06 * => it''s mine,
> >> >it''s yours, 12, 42, 2008/10/06
>
> >> > * * * Example 2):
>
> >> > * * * * * 'it's mine', 'it's yours', 12, 42, '2008/10/06' *=> 'it''s
> >> >mine', 'it''s yours', 12, 42, '2008/10/06'
>
> >> >The tricky part is that the data file may or may not have the string
> >> >fields wrapped in single quotes. *In Example 1), it is not and single
> >> >quotes around fields will be added by another script before sending to
> >> >DB, in Example 2), its fields are already enclosed in single quotes
> >> >and will be sent to DB as is.
>
> >> >Does anybody have handy perl one-liner or script that tackles this?
> >> >Thanks!
>
> >> I don't think this can be a one liner, maybe.
> >> In esscence this does it all:
>
> >> sln
>
> >> #############
> >> # Delim Regex
> >> #############
>
> >> use strict;
> >> use warnings;
>
> >> my $str = "
> >> it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
> >> it's wants it's gets ";
>
> >> while ($str =~ /([^,\n]+)/g)
> >> {
> >> * * * * my $val = $1;
> >> * * * * $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
> >> * * * * $val =~ s/'/''/g;
> >> * * * * $val = "'$val'";
> >> * * * * print "val = $val\n";
>
> >> }
>
> >> __END__
>
> >> output:
>
> >> val_a = 'it''s mine'
> >> val_a = 'it''s yours'
> >> val_a = '12'
> >> val_a = '42'
> >> val_a = '2008/10/06'
> >> val_a = 'it''s mine'
> >> val_a = 'it''s yours'
> >> val_a = '12'
> >> val_a = '42'
> >> val_a = '2008/10/06'
> >> val_a = 'it''s wants it''s gets'- Hide quoted text -
>
> >> - Show quoted text -
>
> >Thanks a lot! *I'm able to adapt it a bit to solve my problem (e.g., I
> >cannot add or remove enclosing single quotes).
>
> Without at least removeing enclosing quotes, the inner quote would be hard to
> substitute. Not impossible, but harder. Its easy to test if there is enclosing
> quote (then add it back later), but it still has to be removed before doing the
> inner quote substitution.
>
> Either way it can be done. How did you do it?
>
> Thanks!
>
> sln- Hide quoted text -
>
> - Show quoted text -

I replace it with a string and then replace it back.

BTW, i cannot use the placeholders to handle strings because this is a
generic function in a perl lib that works like bcp in sybase (i.e.,
loading a given data file into any given table in DB).

On Mon, 6 Oct 2008 12:58:14 -0700 (PDT), "Henry J." <> wrote:

>On Oct 6, 3:48*pm, s...@netherlands.com wrote:
>> On Mon, 6 Oct 2008 11:55:02 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
>> >On Oct 6, 2:25*pm, s...@netherlands.com wrote:
>> >> On Mon, 6 Oct 2008 08:16:38 -0700 (PDT), "Henry J." <tank209...@yahoo.com> wrote:
>
>I replace it with a string and then replace it back.
>
>BTW, i cannot use the placeholders to handle strings because this is a
>generic function in a perl lib that works like bcp in sybase (i.e.,
>loading a given data file into any given table in DB).

Ok, workarounds are acceptable.. good luck.
Btw, I used to do alot of ADO back in the day. In need of a job if you know of any.

sln