Remove Quotes and Commas from a String in MySQL

Question

I'm importing some data from a CSV file, and numbers that are larger than 1000 get turned into 1,100 etc.

What's a good way to remove both the quotes and the comma from this so I can put it into an int field?

Edit:

The data is actually already in a MySQL table, so I need to be able to this using SQL. Sorry for the mixup.

Answer 1

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.

1. The table is just a single column (alpha) that is a varchar.

   mysql> desc t;
   
   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | alpha | varchar(15) | YES  |     | NULL    |       | 
   +-------+-------------+------+-----+---------+-------+
   

2. Add a record

   mysql> insert into t values('"1,000,000"');
   Query OK, 1 row affected (0.00 sec)
   
   mysql> select * from t;
   
   +-------------+
   | alpha       |
   +-------------+
   | "1,000,000" | 
   +-------------+
   

3. Update statement.

   mysql> update t set alpha = replace( replace(alpha, ',', ''), '"', '' );
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   mysql> select * from t;
   
   +---------+
   | alpha   |
   +---------+
   | 1000000 | 
   +---------+
   

So in the end the statement I used was:

UPDATE table
   SET field_name = replace( replace(field_name, ',', ''), '"', '' );

I looked at the MySQL Documentation and it didn't look like I could do the regular expressions find and replace. Although you could, like Eldila, use a regular expression for a find and then an alternative solution for replace.

---

Also be careful with s/"(\d+),(\d+)"/$1$2/ 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 s///g). 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 s/(\d+)?,(\d+)/$1$2/g and in this case I would need a second find and replace to strip the quotes.

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.

>> "1,000,000".sub( /(\d+),(\d+)/, '\1\2' )
# => "1000,000"  
>> "1,000,000".gsub( /(\d+),(\d+)/, '\1\2' )
# => "1000,000"  
>> "1,000,000".gsub( /(\d+)?,(\d+)/, '\1\2' )
# => "1000000"  
>> "1,000,000".gsub( /[,"]/, '' )
# => "1000000"  
>> "1,000,000".gsub( /[^0-9]/, '' )
# => "1000000"