从MySQL中的字符串中删除引号和逗号 [英] Remove Quotes and Commas from a String in MySQL

问题描述

我正在从CSV文件中导入一些数据，大于1000的数字会变成1,100等.

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

从中删除引号和逗号的一种好方法是什么，以便可以将其放入int字段?

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

数据实际上已经存在于MySQL表中，因此我需要能够使用SQL做到这一点.对不起，混淆了.

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

推荐答案

以下是正则表达式的一个很好的例子.您可以在导入之前(更轻松)或更晚(如果SQL导入接受了这些字符)对数据运行查找并替换数据(并非那么容易).但是无论哪种情况，您都可以使用多种方法进行查找和替换，例如编辑器，脚本语言，GUI程序等.请记住，您将要查找并替换 all 坏字符.

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\.]/

以上人员讨论的是，我们不知道您CSV文件中的所有数据.听起来您想从CSV文件中的所有数字中删除逗号和引号.但是因为我们不知道CSV文件中还有什么，所以我们要确保我们不会破坏其他数据.盲目地进行查找/替换可能会影响文件的其他部分.

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.

这篇关于从MySQL中的字符串中删除引号和逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！