title: MySQL table: How can I remove specific characters from all fields?
body: <p>Every week, I have to completely replace the data in several very large MySQL tables. So what I normally do is delete the existing data, import the new data, and then run my usual queries to modify the new data as needed.</p>
  
  <p>Unfortunately, these days I have noticed that the new data contains unwanted characters, such as quotes and extra spaces. With well over 100,000 records in some of these tables (AFAIK), I cannot easily open the data in notepad to strip out unwanted characters, prior to importing. </p>
  
  <p>I realize I could write a separate find and replace query for every single column in every table, like this:</p>
  
  <pre><code>UPDATE mytablename SET mycolumn = REPLACE(mycolumn, '"', '');
  </code></pre>
  
  <p>But having to name every column is a bother. Anyway, I would like to find a more elegant solution. Today, I found a snippet on the internet that looks like a start:</p>
  
  <pre><code> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
          table_name = 'myTable' and ordinal_position = 1 
  </code></pre>
  
  <p>I think the next step might be to loop through the ordinal positions, and then replace and update each column, but I don't know how to do this in MySQL. I also don't know how to stop the loop after the last column is reached, to avoid error messages.</p>
  
  <p>Is there an easy way to do this? Or am I hoping for too much?</p>
  
  <p>I am a beginner, so a clear, simple explanation would be much appreciated.</p>
  
  <p>Thanks in advance.</p>
  
  <p>MORE INFORMATION:</p>
  
  <ol>
  <li>Since my first post, I have discovered that stored procedures are not allowed on my server. Too bad.</li>
  <li>Anyway, I have tried this new code, just to get started:</li>
  </ol>
  
  <p>set @mytablestring='mytable';</p>
  
  <p>set @mycolumnnumber=1;</p>
  
  <p>set @mycolumnname=(SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @mytablestring and ordinal_position = @mycolumnnumber);</p>
  
  <p>SELECT @mycolumnname FROM mytable;</p>
  
  <p>Unfortunately, in the final SELECT query, @mycolumnname is interpreted as a string, not as a column name. So the query does not work. If I could get past this, I believe I could  write some code to loop through the columns by incrementing @mycolumnnumber.</p>
  
  <p>If anyone knows how to solve this, I would really appreciate some help.</p>
  
  <p>Many thanks.</p>