title: MySQL - Replace Character in Columns
body: <p>Being a self-taught newbie, I created a large problem for myself. Before inserting data in to my database, I've been converting apostrophes (') in a string, to double quotes (""), instead of the required back-slash and apostrophe (\'), which MySQL actually requires.</p>
  
  <p>Before my table grows more than the 200,000 rows it already is, I thought it was best to rectify this issue immediately. So I did some research and found the SQL REPLACE function, which is great, but I'm now confused.</p>
  
  <p>In ASP, I was doing this:</p>
  
  <pre><code>str = Replace(str,"'","""")
  </code></pre>
  
  <p>If I look at my database in SQL Workbench, the symbol I converted is now a single quote ("), which has confused me a little. I understand why it changed from double to single, but I don't know which one I'm meant to be changing now. </p>
  
  <p>To go through and rectify my problem using SQL REPLACE, do I now convert single quotes (") to back-slash and apostrophes (\') or do I convert double quotes ("") to back-slash and apostrophes (\')?</p>
  
  <p>For example, this:</p>
  
  <pre><code>SQL = " SELECT REPLACE(myColumn,"""","\'") FROM myTable "
  </code></pre>
  
  <p>or this:</p>
  
  <pre><code>SQL = " SELECT REPLACE(myColumn,""","\'") FROM myTable "
  </code></pre>
  
  <p>I hope I explained myself well, any suggestions gratefully received as always. Any queries about my question, please comment.</p>
  
  <p>Many thanks</p>
  
  <p><strong>-- UPDATE --</strong></p>
  
  <p>I have tried the following queries but still fail to change the ( " ) in the data:</p>
  
  <pre><code>SELECT REPLACE(caption,'\"','\'') FROM photos WHERE photoID = 3371
  SELECT REPLACE(caption,'"','\'') FROM photos WHERE photoID = 3371
  SELECT REPLACE(caption,'""','\'') FROM photos WHERE photoID = 3371
  </code></pre>
  
  <p>Yet if I search:</p>
  
  <pre><code>SELECT COUNT(*) FROM photos WHERE caption LIKE '%"%'
  </code></pre>
  
  <p>I get 16,150 rows.</p>
  
  <p><strong>-- UPDATE 2 --</strong></p>
  
  <p>Well, I have created a 'workaround'. I managed to convert an entire column pretty quickly writing an ASP script, using this SQL:</p>
  
  <pre><code>SELECT photoID, caption FROM photos WHERE caption LIKE '%""%';
  </code></pre>
  
  <p>and then in ASP I did:</p>
  
  <pre><code>caption = Replace(caption,"""","\'")
  </code></pre>
  
  <p>But I would still like to know why I couldn't achieve that with SQL?</p>