CODEWITHSUNDEEP
sqlreplacesubstring
Bill
Bill

Reputation: 95

Removing characters after a substring in a field in SQL

I apologize if this has already been done but I'm having trouble finding an answer.

I'm trying to clean up some data after rebuilding a database. The database is for an art store. The old way of doing it is we'd have a Product table and the Product Name said the name of the art and then "by so and so"

So for example one product might have the name "Mona Lisa by Leonardo Da Vinci"

Recently we wanted to add functionality to the website to be able to index products by artists. So I created an artist table with it's own name field and I no longer want the Artist Name in the Product Name.

So I want to remove everything from the "by" onward.

So instead of "Mona Lisa by Leonardo Da Vinci" just "Mona Lisa"

How can I do this?

Upvotes: 2

Views: 3146

Answers (3)

Josh Weatherly
Josh Weatherly

Reputation: 1730

If using TSQL:

DECLARE @v varchar(150) 
SET @v = 'Mona Lisa By Lenoardo Da Vinci'

--Work
SELECT RTrim(LEFT(@v, CHARINDEX('By',@v)-1)) AS Work

--Other Examples of CHARINDEX()+LEN()+RIGHT/LEFT()
--Creator
SELECT LTrim(RIGHT(@v, LEN(@v)-CHARINDEX('By',@v)-1)) AS Creator

--Both
SELECT RTrim(LEFT(@v, CHARINDEX('By',@v)-1)) AS Work, 
LTrim(RIGHT(@v, LEN(@v)-CHARINDEX('By',@v)-1)) AS Creator

Upvotes: 0

Jonathan M
Jonathan M

Reputation: 17451

If you're sure every product record has " by " followed by the artist name, you can:

  1. Create an artist_name field in the product table
  2. Get each product name
  3. Locate the last " by " in the product name to get the artist name
  4. Put the artist name in an artist_name field
  5. Strip everything after the last " by " in the product name
  6. Update the product record with the new product name and artist_name field.
  7. Create a record for the artist if it doesn't already exist

Upvotes: 1

Michael Ames
Michael Ames

Reputation: 2617

Here's the code you need:

UPDATE Product
SET ProductName = LEFT(ProductName, LEN(ProductName) - CHARINDEX(' yb ', REVERSE(ProductName)) -3 );

This approach ensures that you slice off the text beginning at the last instance of the word " by " surrounded by spaces. Otherwise, "Lady by the Lake by Joe Jones" would become "Lady", and "Abby Road by The Beatles" would become "Ab".

The call to REVERSE flips the order of the characters around so you can search backwards. The call to CHARINDEX finds the first instance of " yb " in this reversed string, which happens to be the last instance of " by " in the original string. Then truncate to that point. Voila.

Good luck!

Upvotes: 4

Related Questions