0

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?

Skullomania
  • 2,225
  • 2
  • 29
  • 65

2 Answers2

3

How about the following:

update t set fruit=Replace(fruit, Concat(itemno, ' - '),'')
Stu
  • 30,392
  • 6
  • 14
  • 33
2

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.

Michael Bruesch
  • 632
  • 7
  • 23
  • REALLY needs a `WHERE` clause, ***just in case*** the assertion that `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` fails for some rows. – MatBailie Nov 11 '21 at 15:57
  • @MatBailie I'm afraid even a `WHERE` is not good enough, it also needs `NULLIF` or `CASE` on the `CHARINDEX`. See https://stackoverflow.com/a/67259538/14868997 – Charlieface Nov 11 '21 at 17:53