Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?

Question

Sorry if my title doesn't make much sense, I wasn't sure how to word it though.

Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column imported as [Address ; Address]. Is there a way to remove everything after the first Address, including the ';' in one query?

I thought about the REPLACE function, but wasn't sure how to update every record at once since each store has a different Address. I'll include a screenshot so my question makes more sense. Any suggestions? I was thinking since there's a delimiter it might not be too hard to update, but I'm lost as to how.

Any help is greatly appreciated. Thank you.

Ignore the first record as it was previously setup.

Technically using Progress ODBC Query Tools. I can update using SQL Server 2016 though.

Answer 1

You can do this by using the CHARINDEX() function to find the position of the semicolon, coupled with SUBSTRING() to update the field with the data prior to the semicolon.

UPDATE test_table SET addr=SUBSTRING(addr, 0, CHARINDEX(';',addr))

If there are any legitimate semicolons in the data then it will of course remove some of the address, so please check the data first to ensure there is only one semicolon in each record.