0

suppose a table is like this

ID            Desc
1          I am a boy
2          I am a boy xyz
3          I am a boy abc
4          xyzab I am a boy
5          life is beautiful
6          blank

now my requirement is whenever in a row the phrase “I am a boy” is found it will be removed but the rest of the part will remain as it is. If that phrase is not there there will be no change. So for the above table after the query it will look like this

ID          Desc
1          <blank>
2           xyz
3           abc
4          xyzab
5         life is beautiful
6          blank
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Deepshubhra Bhattacharya
  • 3
  • 2

2 Answers2

1

You can try the following replacement. Note that I wrap the replaced description column to remove any whitespace leftover from removing the phrase I am a boy.

UPDATE yourTable
SET Desc = TRIM(REPLACE(Desc, 'I am a boy', ''))
WHERE Desc LIKE '%I am a boy%'

The WHERE clause restricts the UPDATE operation to those records containing the boy string only. In this particular case, it might be redundant. But in general adding a WHERE clause can save time by avoiding modifying a record when no change is intended. Also, in general (though not here), a WHERE clause can even speed up the update when the where column has an index.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    Beat me to it. Interesting idea throwing the TRIM in there -- it's probably what the OP really wants, even if not really what was asked for. – A C Feb 05 '17 at 08:34
  • The WHERE clause is redundant – Strawberry Feb 05 '17 at 08:50
  • @Strawberry I don't want records not eligible for an update to even be touched. – Tim Biegeleisen Feb 05 '17 at 08:53
  • 1
    @Strawberry - What Tim means is this: Without the WHERE clause, the value `xyzab` will be **updated** to itself. Even though logically nothing changes, in practice the assignment still takes place (which means overhead), UNDO is still generated, additional overhead if there are indexes and constraints that must be checked is still incurred. The DB engine doesn't check to see if the "new" value is the same as the "old" (in essence, the DB engine doesn't do for you what the WHERE clause can). **Not** having the WHERE clause is a very common mistake! –  Feb 05 '17 at 21:32
  • @mathguy I don't think that the assignment takes place. In short, I think there's no impact on performance. Can you demonstrate, or otherwise reference documentation confirming that behaviour? (And note that desc is a reserved word). – Strawberry Feb 06 '17 at 00:27
  • 1
    @Strawberry - tell the OP and Tim about desc, why are you telling me? Re the behavior of UPDATE, did you do your own research? You questioned Tim, he answered you, I explained to you why he is right. Believe what you want, why do I need to demonstrate anything to you? –  Feb 06 '17 at 00:40
  • @Strawberry Even though we might be able to eschew a `WHERE` clause here, I would hate to be the DBA who inherits such a query and wonders why it is missing. If you have documentation supporting your concerns then please drop a link here. – Tim Biegeleisen Feb 06 '17 at 00:51
  • @TimBiegeleisen That's a fair point - and important one. But the argument about performance is spurious. On my test on an indexed table of ca. 130000 rows (of which roughly 80% match), performance is faster WITHOUT the WHERE clause. – Strawberry Feb 06 '17 at 00:54
0

First of all it is better to change the column name Desc to something else, because it is a reserved keyword. Have a look at this

The following query can solve your problem. I have changed the column name here to Dsc.

UPDATE mytable
SET Dsc = REPLACE(Dsc, 'I am a boy', '');

SQLFiddle

Community
  • 1
  • 1
jophab
  • 5,356
  • 14
  • 41
  • 60