Tuesday, March 04, 2008 2:12 PMHi 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.
Tuesday, March 04, 2008 2:26 PM
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.
UpdateTable_1 set MyField=REPLACE(MyField,'msdn forums','')
Tuesday, March 04, 2008 2:32 PMIs 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.
Tuesday, March 04, 2008 2:41 PMModerator
Unfortunately you cannot use this on a text field unless you CAST or CONVERT it.
Tuesday, March 04, 2008 3:12 PMYup 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:
SET myField = REPLACE(CONVERT(VARCHAR(MAX), myField),
'Test I want to replace',
'This is my replace text')