Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>My guess here is that because the data was able to import that the field is actually a varchar or some character field, because importing to a numeric field might have failed. Here was a test case I ran purely a MySQL, SQL solution.</p> <ol> <li><p>The table is just a single column (alpha) that is a varchar.</p> <pre><code>mysql&gt; desc t; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | alpha | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ </code></pre></li> <li><p>Add a record</p> <pre><code>mysql&gt; insert into t values('"1,000,000"'); Query OK, 1 row affected (0.00 sec) mysql&gt; select * from t; +-------------+ | alpha | +-------------+ | "1,000,000" | +-------------+ </code></pre></li> <li><p>Update statement.</p> <pre><code>mysql&gt; update t set alpha = replace( replace(alpha, ',', ''), '"', '' ); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql&gt; select * from t; +---------+ | alpha | +---------+ | 1000000 | +---------+ </code></pre></li> </ol> <p>So in the end the statement I used was:</p> <pre><code>UPDATE table SET field_name = replace( replace(field_name, ',', ''), '"', '' ); </code></pre> <p>I looked at the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace" rel="nofollow noreferrer">MySQL Documentation</a> and it didn't look like I could do the regular expressions find <em>and replace</em>. Although you could, like <a href="https://stackoverflow.com/questions/7917/remove-quotes-and-commas-from-a-string-in-mysql#8135">Eldila</a>, use a regular expression for a find and then an alternative solution for replace.</p> <hr> <p>Also be careful with <code>s/"(\d+),(\d+)"/$1$2/</code> because what if the number has more then just a single comma, for instance "1,000,000" you're going to want to do a global replace (in perl that is <code>s///g</code>). But even with a global replace the replacement starts where you last left off (unless perl is different), and would miss the every other comma separated group. A possible solution would be to make the first (\d+) optional like so <code>s/(\d+)?,(\d+)/$1$2/g</code> and in this case I would need a second find and replace to strip the quotes.</p> <p>Here are some ruby examples of the regular expressions acting on just the string "1,000,000", notice there are NOT double quote inside the string, this is just a string of the number itself.</p> <pre><code>&gt;&gt; "1,000,000".sub( /(\d+),(\d+)/, '\1\2' ) # =&gt; "1000,000" &gt;&gt; "1,000,000".gsub( /(\d+),(\d+)/, '\1\2' ) # =&gt; "1000,000" &gt;&gt; "1,000,000".gsub( /(\d+)?,(\d+)/, '\1\2' ) # =&gt; "1000000" &gt;&gt; "1,000,000".gsub( /[,"]/, '' ) # =&gt; "1000000" &gt;&gt; "1,000,000".gsub( /[^0-9]/, '' ) # =&gt; "1000000" </code></pre>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
    2. VO
      singulars
      1. This table or related slice is empty.
    3. VO
      singulars
      1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload