Replace quotation marks in sql query in perl script

Question

I have a text file of data that I am importing into a MySQL database. Some of the lines unfortunatley contain quotation marks, which causes my SQL queries to go haywire. I would like to get rid of any field that has quotation marks, or at very least ignore them in my query.

I found something that might work, but being as this is run through a Perl script I am having issues "escaping" the quotation marks. I really don't know how and can't figure it out. I would like to just search through my table and delete any quotation marks (") that it may find or replace it with a single quotation mark or space or anything really.

my $myreplacequery = "REPLACE(s.Title, '"','')";
$sth = $dbh->prepare($myreplacequery);
$sth->execute;

Anyone have any ideas?

Thanks!

Could you please edit my $replacequery to $myreplacequery? — user1726343
– user1726343, Commented Nov 1, 2012 at 7:33
I can't get this to work, so I decided to just use perl to find/replace quotes in the file that I am inputting into the tables originally before it even gets to SQL. Hopefully this will work, can't tell yet. — user1026801
– user1026801, Commented Nov 8, 2012 at 5:37

sufleR · Accepted Answer · 2012-11-01 07:44:44Z

1

Change query to UPDATE on this table:

 update tablename set title = REPLACE(title,'\"','\'') where title like '%\"%'

answered Nov 1, 2012 at 7:44

sufleR

2,99320 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RobEarl · Accepted Answer · 2012-11-01 08:25:24Z

1

Perl has q and qq (quote-like operators) for this kind of situation. They allow you to choose the quote character to use. q acts like a single-quote (') and doesn't interpolate (expand variables) while qq acts like a double quote (") and does.

my $replacequery = q{REPLACE(s.Title, '"','')};

edited Nov 1, 2012 at 8:25

answered Nov 1, 2012 at 7:35

RobEarl

7,9226 gold badges38 silver badges52 bronze badges

Comments

ikegami · Accepted Answer · 2012-11-01 08:30:07Z

0

You actually want to pass a string consisting of a single quote to REPLACE for its 3rd arg, but you're passing an empty string. The necessary SQL is:

REPLACE(s.Title, '"', '\'')

To create that string in Perl, you could use either of the following string literals:

"REPLACE(s.Title, '\"', '\\'')"    # Produces: REPLACE(s.Title, '"', '\'')

qq{REPLACE(s.Title, '"', '\\'')}   # Produces: REPLACE(s.Title, '"', '\'')

Notice how " needed to be escaped. Without it, Perl would see the following string literal (followed by junk):

"REPLACE(s.Title, '"
^                  ^
|                  |
start              end
of string          of string
literal            literal

edited Nov 1, 2012 at 8:30

answered Nov 1, 2012 at 8:18

ikegami

391k17 gold badges291 silver badges555 bronze badges

Collectives™ on Stack Overflow

Replace quotation marks in sql query in perl script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related