SQL query to remove everything after a comma in varchar string values in a column

Question

I created a database for addresses, however, I made a mistake of concatenating the unit number with the road address which happened to include the unit number as well. So now, the unit number is repeated in some rows.

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

So if you look in the table above in the last 6 records, in the physical_address column, the unit numbers like 10M, 11B, 11V etc are repeated.

Is there any query I can run to remove everything after the last , in each row? The type is varchar for the column if that helps. Also, keeping in mind that some addresses don't have any , in them.

EDIT what I have tried:

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

This unfortunately removes everything after the first comma, not the last.

This is on a database named Dolt

Answer 1

You can use this:

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

Answer 2

This works in MySQL, (but not in Dolt!).

Dolt claims:

Dolt's goal is to be compliant with the MySQL dialect, with every query and statement that works in MySQL behaving identically in Dolt.

But there is no info with which version of mysql this compatibility is, and most things do not work correct (which is the conclusion from a small test using Dolt version 0.35.3)

Anyway, when using MySQL, this can be done: DBFIDDLE for the MySQL solution

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

EDIT: (Because it always starts working when you say that it will never work):

Below is a session in Dolt, which seems to work, despite the red-X in the list of functions near REGEXP_REPLACE

D:\dolt-windows-amd64\bin>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>

P.S. I still do no like seeing a mysql> prompt ....