How do I update column in SQL by removing preceding characters in column?-CodePudding

I need help in updating records in my table [Fruit]. Currently, I have number - name (ie 1234 - Apple) value which I would like to replace with Apple from all the records (the number preceding is not the same).

Date       ItemNo    Fruit   
---------------------------------
01/10/10   1234      1234 - Apple
01/12/10   5546      5546 - Banana

I want to only remove the preceding number - from the column Fruit.

Date       ItemNo    Fruit   
---------------------------------
01/10/10   1234      Apple
01/12/10   5546      Banana

How do I do this for all rows in my table?

CodePudding user response：

How about the following:

update t set fruit=Replace(fruit, Concat(itemno, ' - '),'')

CodePudding user response：

If the Fruit field will always have a dash '-' and it will always have a space between the dash and the text to the right of it then you can use substring with charindex to extract the string on the right of the dash like so:

update F 
set F.Fruit = substring(F.Fruit, charindex('-', F.Fruit)   2, len(F.Fruit))
from Fruit as F

charindex gets the location of the dash, then returns the location 2 to the right of that, which will be the start of your string.