CODEWITHSUNDEEP
mysqlsqlperldbi
user1026801
user1026801

Reputation: 37

Replace quotation marks in sql query in perl script

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!

Upvotes: 0

Views: 1461

Answers (3)

ikegami
ikegami

Reputation: 386706

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

Upvotes: 0

RobEarl
RobEarl

Reputation: 7912

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, '"','')};

Upvotes: 1

sufleR
sufleR

Reputation: 2973

Change query to UPDATE on this table: 

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

Upvotes: 1

Related Questions