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

there is a list of [string functions](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html) available, with a short description of what they do. Which one did you try ? — Luuk, Jan 09 '22 at 16:03
@Luuk I edited the post to include the query that came closest. It was removing all after the first comma though, not the last. — Tendekai Muchenje, Jan 09 '22 at 16:12
`CHARINDEX` is not in the list of MySQL string function, i mentioned earlier.. — Luuk, Jan 09 '22 at 16:15
@Luuk so I am actually running these queries in a software called Dolt from Dolthub. I have been it just has a >> dolt sql -q "" interface and so far I have been running MySQL queries and it has been working fine. The query i posted above, I found from an old SO post which now I am realizing is actually a SQLServer post. Otherwise I don't know what exactly Dolt is using, but I have been running MySQL queries because that is the format I know. — Tendekai Muchenje, Jan 09 '22 at 16:19

Luuk · Answer 1 · 2022-01-09T17:07:24.987

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

score 0 · Answer 2 · edited Jan 09 '22 at 16:17

0

You can use this:

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

edited Jan 09 '22 at 16:17

jarlh

42,561
8
45
63

answered Jan 09 '22 at 16:09

Munir ahmed

1

So looking at your query, it should work, but when I run it on the full table it gives me an error. I had not included the rest of the columns, but here is a gist with all the columns in the table and sample data. https://gist.github.com/vaMuchenje/926b71d7290ca187af73cfb30422b462 When I run the query, it gives me an error saying: `duplicate primary key given: ["NY","",2021-03-02 00:00:00]` . The column I am targeting is physical_address. – Tendekai Muchenje Jan 09 '22 at 16:32
you should have provided the complete table definition before complaining about `duplicate primary key`.... – Luuk Jan 09 '22 at 16:43

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

2 Answers2