2

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?

Bill
  • 95
  • 2
  • 10
  • 1
    You definitely want to save the artist name. Don't just delete it from the product name. – Jonathan M Jul 21 '11 at 21:57
  • Bill: Will you clarify? Your question makes it sound like you've already copied over the artist name and all you want to do is remove it from the product name. Do you need code for extracting the artist name, too? – Michael Ames Jul 21 '11 at 22:15
  • No. The artist table is already built. I just need to clean up the product name. – Bill Jul 26 '11 at 14:10

3 Answers3

4

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!

Michael Ames
  • 2,607
  • 1
  • 16
  • 22
  • This worked for some of them. But others it deleted the letter just before the by for some reason. – Bill Jul 26 '11 at 14:20
  • Can you give an example of one that worked and one that didn't? I'm wondering if you may have double spaces in some cases. – Michael Ames Jul 26 '11 at 17:15
1

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
Jonathan M
  • 17,145
  • 9
  • 58
  • 91
0

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
Josh Weatherly
  • 1,710
  • 1
  • 15
  • 27