CODEWITHSUNDEEP
sql-server
Softdog
Softdog

Reputation: 13

SQL remove part of text in a column

I´m trying to get rid of parts of text in a column. It´s a table with 100.000´s rows and basically its Firstname; Lastname, where Lastname contain Firstname as well, in a lot of them but far from all.

Ex:

Firstname Lastname
Magnus    Johansson
Anders    Anders Jansson
Stefan    Stefan Andersson
Emma      Svensson
Annelie   Annelie Nilsson

I would like to clean it up to:

Firstname Lastname
Magnus    Johansson
Anders    Jansson
Stefan    Andersson
Emma      Svensson
Annelie   Nilsson

I have tried using SUBSTR and RIGHT with a few alterations, with no success

RIGHT(Lastname, LEN(Lastname) - LEN(Firstname)) as New_Lastname

RIGHT as well as SUBSTR and others I have looked at requires a fixed int value to determine what should be replaced/removed.

Anyone know how to proceed with a variable length on the text that need to be altered?

Upvotes: 1

Views: 208

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try with REPLACE:

REPLACE (Lastname, Firstname + ' ', '')

If Firstname is contained in Lastname then it will simply be replaced by an empty string.

Note: You need to add an extra space to the string_pattern of REPLACE so as to avoid replacing any Firsname values that are part of Lastname, like in this case:

Firstname Lastname
-------------------
Mark      Markuson

As a final note, you might have to apply LTRIM to REPLACE, like:

LTRIM(REPLACE (Lastname, Firstname + ' ', ''))

so as to trim any leading spaces after replace has taken place.

Demo here

Upvotes: 2

Related Questions