0

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.

enter image description here

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

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.

3
  • That worked perfectly, thank you! Any suggestions on how to retain the semicolon, but remove everything after it? I was looking at the data and it appears every address record contains a semicolon at the end so I'm guessing I'll need to retain it. – lFreightTrain Apr 15 '20 at 13:08
  • 1
    Just change it to UPDATE test_table SET addr=SUBSTRING(addr, 0, CHARINDEX(';',addr) + 1) – Tony Hinkle Apr 15 '20 at 13:09
  • That also worked. Thank you for the assistance. – lFreightTrain Apr 15 '20 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.