mysql除最后的逗号,从MySQL中的字符串中删除引号和逗号

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.