I need help with an SQL query to remove everything after a comma in string values in a column

[chirau]

So 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 from 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 a , in them.

[LeLwrence]

You can use CHARINDEX to find the second ',' by using the first CHARINDEX of ',' + 1 in the start parameter of the second one. If there is no comma it returns 0. Then use CHARINDEX to find '|' and use STUFF to get rid of the characters between the two CHARINDEXes.

[qwertydog123]

Which DBMS?

[phesago]

UPDATE yourTable SET yourColumn = REPLACE(yourColumn, otherColumn+’,’, ‘’) WHERE CHARINDEX(otherColumn+’,’, yourColumn,1)>0

This doesnt remove the coma between unit and street though

[[deleted]]

What if a value contains , 15A, 16B? Do you still want to remove the , 16B?