mysql - special - sql regular expression example




Remove Quotes and Commas from a String in MySQL (3)

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.


Here is a good case for regular expressions. You can run a find and replace on the data either before you import (easier) or later on if the SQL import accepted those characters (not nearly as easy). But in either case, you have any number of methods to do a find and replace, be it editors, scripting languages, GUI programs, etc. Remember that you're going to want to find and replace all of the bad characters.

A typical regular expression to find the comma and quotes (assuming just double quotes) is: (Blacklist)

/[,"]/

Or, if you find something might change in the future, this regular expression, matches anything except a number or decimal point. (Whitelist)

/[^0-9\.]/

What has been discussed by the people above is that we don't know all of the data in your CSV file. It sounds like you want to remove the commas and quotes from all of the numbers in the CSV file. But because we don't know what else is in the CSV file we want to make sure that we don't corrupt other data. Just blindly doing a find/replace could affect other portions of the file.


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"

How can I get an image or link to show up in an Oracle APEX Interactive Report?

Make sure the Display Type of the column is "Standard Report Column":

This Display Type renders the HTML; some other Display Types escape it.







string