none
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.
    Tuesday, March 04, 2008 2:12 PM

Answers

  •  

    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

    Tuesday, March 04, 2008 2:26 PM

All replies

  •  

    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

    Tuesday, March 04, 2008 2:26 PM
  • 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.
    Tuesday, March 04, 2008 2:32 PM
  • Unfortunately you cannot use this on a text field unless you CAST or CONVERT it.

     

    Tuesday, March 04, 2008 2:41 PM
    Moderator
  • Yup I used a CONVERT then your code which worked perfectly! Thanks again! Smile

    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')



    Tuesday, March 04, 2008 3:12 PM