MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE Table1 (physical_address varchar(100),city varchar(100));

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');
SELECT
    physical_address,
    city
FROM Table1
    


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


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


physical_address
city


1 AGATE COURT
New York City

1 ANGELAS PLACE, 1A
New York City

1 ARLINGTON COURT
New York City

1 AVENUE J
New York City

1 BAY CLUB DRIVE, 10M
New York City

1 BAY CLUB DRIVE, 11B
New York City

1 BAY CLUB DRIVE, 11V
New York City

1 BAY CLUB DRIVE, 12H
New York City

1 BAY CLUB DRIVE, 14S
New York City

1 BAY CLUB DRIVE, 15B
New York City

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

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