How to remove part of ending string and update columns in SQL Server-CodePudding

I have strings in my table Location in column PlaceForeign:

SOMELONGTEXT - BERLIN - BERLIN
someOtherText - LUANDA - LUANDA
somethingsomething - PARIS - PARIS

I want to remove last occurrence of a city and to have like this:

SOMELONGTEXT - BERLIN
someOtherText - LUANDA
somethingsomething - PARIS

CodePudding user response：

If you really want to update, then use:

UPDATE Location
SET PlaceForeign = SUBSTRING(PlaceForeign, 1, CHARINDEX('-', PlaceForeign, CHARINDEX('-', PlaceForeign)   1) - 2);

The logic here is to take a substring from the start of the place until 2 characters before the occurrence of the second dash.

Here is a demo which uses a select to show that the substring logic is working.