Removing or Replacing Part of a String in a Field?

Question

Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:

- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums

Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?

Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.

Appreciate the responses, Onam.

Answer 1

 

Hi Onam

 

I don't know if i really got your question but removing a part of string contained in a specific field can be performed like this.

 

Update Table_1 set MyField=REPLACE(MyField,'msdn forums','')

 

Best regards

Raimund

Answer 2

Is it possible to do that on a Text Field? Works with VARCHAR however not with a TEXT type.

Thanks for the reply, Onam.

*EDIT* Bah I could do a CONVERT on the field before doing a replace.

Answer 3

Unfortunately you cannot use this on a text field unless you CAST or CONVERT it.

 

Answer 4

Yup I used a CONVERT then your code which worked perfectly! Thanks again!

For reference I have posted the code below so if anyone has trouble with the same thing I experienced it can be used as a guidance:

Code Snippet

UPDATE Table_1

SET myField = REPLACE(CONVERT(VARCHAR(MAX), myField),

'Test I want to replace',

'This is my replace text')