CODEWITHSUNDEEP
sqlsql-server
lonelydev101
lonelydev101

Reputation: 1901

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

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

Upvotes: 0

Views: 601

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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.

Upvotes: 1

Related Questions