在MySQL中从字符串中删除引号和逗号

Question

在MySQL中从字符串中删除引号和逗号

mysqlregexstring

16

我正在从一个 CSV 文件中导入一些数据，数值大于 1000 的数字会变成类似 1,100 的形式。

有什么好的方法可以去掉引号和逗号，以便将其放入 int 字段中呢？

编辑：

实际上，数据已经在 MySQL 表中了，因此我需要使用 SQL 来完成这个操作。抱歉造成混淆。

- Steve Willard

8个回答

2

这是一个使用正则表达式的好例子。您可以在导入数据之前（更容易）或之后（不太容易）对数据进行查找和替换。但无论哪种情况，您都可以使用多种方法进行查找和替换，包括编辑器、脚本语言、GUI程序等。请记住，您需要找到并替换所有的坏字符。

查找逗号和引号（假设只有双引号）的典型正则表达式为：（黑名单）

/[,"]/

或者，如果你认为未来可能会有一些变化，这个正则表达式将匹配除数字或小数点以外的任何内容。（白名单）

/[^0-9\.]/

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

- Joseph Pecoraro

0

这是 PHP 的方法：

$stripped = str_replace(array(',', '"'), '', $value);

W3Schools页面链接

- user316

0

对于变化的问题，解决方案基本上相同。

你需要使用正则表达式的where子句来运行选择查询。

类似以下的语句：

Select *
  FROM SOMETABLE
  WHERE SOMEFIELD REGEXP '"(\d+),(\d+)"'

对于这些行中的每一行，您想要执行以下正则表达式替换 s/"(\d+),(\d+)"/$1$2/，然后使用新值更新字段。

在对任何文件或数据库进行大规模更改之前，请认真考虑并备份。因为每当您使用正则表达式时，如果有您忽略的情况，您可能会严重破坏数据。请谨慎操作。

- Eldila

0

你可以使用这个perl命令。

Perl -lne 's/[,|"]//; print' file.txt > newfile.txt

你可能需要稍微调整一下，但它应该能解决问题。

- Eldila

0

我的命令可以删除所有的','和'"'。

为了更严格地转换字符串"1,000"，您需要使用以下命令。

Perl -lne 's/"(\d+),(\d+)"/$1$2/; print' file.txt > newfile.txt

- Eldila

0

实际上，nlucaroni，您的情况并不完全正确。您的示例中没有包含双引号，因此

id,age,name,...
1,23,phil,

不符合我的正则表达式。它需要格式为“XXX，XXX”。我想不出它什么情况下会匹配错误。

所有以下示例都不包括定界符在正则表达式中：

"111,111",234
234,"111,111"
"111,111","111,111"

如果您能想到一个反例，请告诉我。

谢谢！

- Eldila

-1

Daniel和Eldila的答案有一个问题：他们删除了整个文件中的所有引号和逗号。

当我需要做类似的事情时，我通常会先将所有分隔引号和（通常）分号替换为制表符。

搜索： ";"
替换： \t

由于我知道受影响的值将在哪一列，因此我会进行另一个搜索和替换：

搜索：^([\t]+)\t([\t]+)\t([0-9]+),([0-9]+)\t
替换：\1\t\2\t\3\4\t

... 假设带有逗号的值在第三列。

您需要以“^”开头，以确保它从行首开始。然后，您重复 ([0-9]+)\t 直到您想要保留它们的列数。

([0-9]+),([0-9]+) 搜索具有数字、逗号和另一个数字的值。

在替换字符串中，我们使用\1和\2来保留编辑行中的值，并用\t（制表符）将它们分开。然后我们放置\3\4（没有制表符），将数字的两个组成部分放在一起，不带逗号。之后的所有值都将保持不变。

如果您需要文件以分号分隔元素，则可以继续将制表符替换为分号。但是，如果您省略引号，则必须确保文本值本身不包含任何分号。这就是为什么我更喜欢使用TAB作为列分隔符的原因。

我通常在支持RegExp的普通文本编辑器（EditPlus）中执行此操作，但是相同的正则表达式也可以在任何编程语言中使用。

- BlaM

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Joseph Pecoraro · Accepted Answer

我的猜测是，由于数据导入成功，所以该字段实际上是一个 varchar 或一些字符型字段，因为导入到数值型字段可能会失败。这是我在 MySQL 中运行的纯 SQL 测试案例。

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    |       | 
+-------+-------------+------+-----+---------+-------+

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" | 
+-------------+

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 | 
+---------+

所以最终我使用的语句是：

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

我查看了MySQL文档，似乎无法使用正则表达式进行查找和替换。虽然像Eldila那样，您可以使用正则表达式进行查找，然后使用另一种解决方案进行替换。

请注意s/"(\d+),(\d+)"/$1$2/的使用，因为如果数字中有超过一个逗号，例如 "1,000,000"，则需要进行全局替换（在Perl中是 s///g）。但即使进行全局替换，替换仍然从上次结束的地方开始（除非Perl不同），并且会遗漏每个组之间以逗号分隔的逗号。一种可能的解决方案是使第一个 (\d+) 成为可选项，如此进行：s/(\d+)?,(\d+)/$1$2/g，在这种情况下，您需要进行第二次查找和替换以去掉引号。

以下是一些针对字符串 "1,000,000" 的Ruby正则表达式示例，请注意字符串内部没有双引号，这只是数字本身的字符串。

>> "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"