SQL查询，以删除列中varchar字符串值中逗号后的所有内容

Question

我创建了一个地址数据库，但我犯了一个错误，将单元号与道路地址连接起来，而道路地址恰好也包含单元号。现在，单元号在一些行中重复。

+----------------------------+---------------+
| physical_address           | city          |
+----------------------------+---------------+
| 1 AGATE COURT              | New York City |
| 1 ANGELAS PLACE, 1A, 1A    | New York City |
| 1 ARLINGTON COURT          | New York City |
| 1 AVENUE J                 | New York City |
| 1 BAY CLUB DRIVE, 10M, 10M | New York City |
| 1 BAY CLUB DRIVE, 11B, 11B | New York City |
| 1 BAY CLUB DRIVE, 11V, 11V | New York City |
| 1 BAY CLUB DRIVE, 12H, 12H | New York City |
| 1 BAY CLUB DRIVE, 14S, 14S | New York City |
| 1 BAY CLUB DRIVE, 15B, 15B | New York City |
+----------------------------+---------------+

因此，如果您查看上表中最后6条记录的physical_address列，则会重复10M、11B、11V等单位编号。

我是否可以运行任何查询来删除每行中最后一个,之后的所有内容？如果有帮助的话，列的类型是varchar。此外，请记住，有些地址中没有任何,。

编辑我尝试过的内容：

UPDATE sales
SET MyAddress = LEFT(MyAddress, CHARINDEX(',', MyAddress) - 1)
WHERE CHARINDEX(',', MyAddress) > 0

不幸的是，这会删除第一个逗号之后的所有内容，而不是最后一个逗号。

这是在一个名为Dolt 的数据库上

Answer 1

这适用于MySQL(但不适用于Dolt！)。

Dolt声明：

Dolt的目标是兼容MySQL方言在MySQL中工作的查询和语句在Dolt中表现相同。

但没有信息表明这种兼容性与mysql的哪个版本兼容，而且大多数事情都不正确(这是使用Dolt版本0.35.3进行的小型测试得出的结论)

不管怎样，当使用MySQL时，这可以做到：MySQL解决方案的DBFIDDLE

UPDATE Table1
SET physical_address = REGEXP_REPLACE(physical_address,'(,[^,]*),[^,]*','$1')

编辑：(因为当你说它永远不会工作时，它总是开始工作)：

下面是Dolt中的一个会话，尽管REGEXP_REPLACE 附近的函数列表中有红色的-X，但它似乎有效

D:dolt-windows-amd64bin>dolt sql-client -P 3307
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> CREATE DATABASE test;
mysql> use test;
mysql> CREATE TABLE Table1 (physical_address varchar(100),city varchar(100));
mysql> INSERT INTO Table1 VALUES
->
-> ('1 AGATE COURT              ','New York City'),
-> ('1 ANGELAS PLACE, 1A, 1A    ','New York City'),
-> ('1 ARLINGTON COURT          ','New York City'),
-> ('1 AVENUE J                 ','New York City'),
-> ('1 BAY CLUB DRIVE, 10M, 10M ','New York City'),
-> ('1 BAY CLUB DRIVE, 11B, 11B ','New York City'),
-> ('1 BAY CLUB DRIVE, 11V, 11V ','New York City'),
-> ('1 BAY CLUB DRIVE, 12H, 12H ','New York City'),
-> ('1 BAY CLUB DRIVE, 14S, 14S ','New York City'),
-> ('1 BAY CLUB DRIVE, 15B, 15B ','New York City');
mysql> SELECT * FROM Table1;
+-----------------------------+---------------+
| physical_address            | city          |
+-----------------------------+---------------+
| 1 ANGELAS PLACE, 1A, 1A     | New York City |
| 1 ARLINGTON COURT           | New York City |
| 1 BAY CLUB DRIVE, 11B, 11B  | New York City |
| 1 BAY CLUB DRIVE, 12H, 12H  | New York City |
| 1 AVENUE J                  | New York City |
| 1 BAY CLUB DRIVE, 14S, 14S  | New York City |
| 1 AGATE COURT               | New York City |
| 1 BAY CLUB DRIVE, 11V, 11V  | New York City |
| 1 BAY CLUB DRIVE, 10M, 10M  | New York City |
| 1 BAY CLUB DRIVE, 15B, 15B  | New York City |
+-----------------------------+---------------+
mysql> UPDATE Table1  SET physical_address = REGEXP_REPLACE(physical_address,'(,[^,]*),[^,]*','$1')
-> ;
mysql> SELECT * FROM Table1;
+-----------------------------+---------------+
| physical_address            | city          |
+-----------------------------+---------------+
| 1 ANGELAS PLACE, 1A         | New York City |
| 1 ARLINGTON COURT           | New York City |
| 1 BAY CLUB DRIVE, 10M       | New York City |
| 1 BAY CLUB DRIVE, 11B       | New York City |
| 1 BAY CLUB DRIVE, 14S       | New York City |
| 1 BAY CLUB DRIVE, 15B       | New York City |
| 1 AVENUE J                  | New York City |
| 1 AGATE COURT               | New York City |
| 1 BAY CLUB DRIVE, 11V       | New York City |
| 1 BAY CLUB DRIVE, 12H       | New York City |
+-----------------------------+---------------+
mysql>

附言：我仍然不喜欢看到mysql>提示。。。。

Answer 2

您可以使用这个：

UPDATE table_name
SET column_name = SUBSTRING_INDEX(
column_name,
',',
LENGTH(column_name) - LENGTH(REPLACE(column_name, ',', ''))
);