Hi,
I want to escape single quotes in the result of a query run in a shell
script. I think REPLACE is the right function to use.
If, at the mysql prompt, I enter
mysql> select replace(name, '\'', '\\\'') from cust_details where name like
'%\'%' limit 10;
I get
+---------------------------------+
| replace(name, '\'', '\\\'') |
+---------------------------------+
| WELLINGTON ASM\'S |
| MAGS N\' MOVIES (TRACK & TRAC |
| M/C\'TRACT:CAMPAIGN PALACE |
| ANDERSON & O\'LEARY LTD |
| CHESTER\'S PLUMBING & BATHROOM |
| BENNETT\'S GOVERNMENT BOOK SHO |
| *CHRISTIAN CHILDREN\'S FUND (NZ |
| NATURE\'S SUNSHINE PRODUCTS |
| HILL\'S FLOORINGS LTD |
| *O\'NEALE WOODCRAFTS LTD |
+---------------------------------+
Which is perfect - the strings can be sent back in to a mysql session
correctly.
If, however, I put the exact same command in to a file , myfile.sql, and go
$ mysql mydb < myfile.sql
I get
replace(name, '\'', '\\\'')
WELLINGTON ASM\\'S
MAGS N\\' MOVIES (TRACK & TRAC
M/C\\'TRACT:CAMPAIGN PALACE
ANDERSON & O\\'LEARY LTD
CHESTER\\'S PLUMBING & BATHROOM
BENNETT\\'S GOVERNMENT BOOK SHO
*CHRISTIAN CHILDREN\\'S FUND (NZ
NATURE\\'S SUNSHINE PRODUCTS
HILL\\'S FLOORINGS LTD
*O\\'NEALE WOODCRAFTS LTD
Using these as the input to another mysql session doesn't work, as the first
\ escapes the second, the ' terminates the string, and the rest of the
string is a syntax error.
I have used various numbers of \'s, and even char(92), but nothing gives me
the correct result.
I am sure this wheel must have been invented before - anyone out there done
it, or am I on the wrong track?
For info, the full command I am trying to run is:
mysql mydb <<!
SELECT concat(concat('update crm_cust set name = \'',
replace(name, "'", "\\\'")), '\','),
concat('bill_to = \'', billto_account, '\','),
concat('repcode = \'', repcode, '\','),
concat('rpt_to = \'', ifnull(rpt_master, cust_details.account), '\','),
'priority = ', priority,
concat('where account = \'', cust_details.account, '\';')
from crm_update_cust,
cust_details left join report_masters
on cust_details.account = report_masters.account
where cust_details.account = crm_update_cust.account
and cust_details.name like '%\'%' limit 10;
!
with the result being piped in to another mysql (either directly or via a
file)
TIA
Quentin Bennett
Transport Systems Division
Infinity Solutions
www: http://www.infinity.co.nz
mailto:[EMAIL PROTECTED]
Phone: +64 9 358 9720
Fax: +64 9 309 4142
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
