Remove a string in a text field

In a stored procedure, How can I remove a text string in a  text field.

say:
Textfield = 'Something ABCSomething else'

I want to remove the 'ABC' from the text field,

so that when I am done,
Texfield =  'Something Something else'

Thanks in Advance,

misdevelopersAsked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
The only way you can do this is to use CAST to cast the TEXT data to VARCHAR. The 'problem' is that VARCHAR is only limited to 8000 characters total, so if your text string is longer than that, you are hosed. If it is less than 8000 characters, you can do
DECLARE @Text VARCHAR(8000)
SET @Text

SELECT CAST(REPLACE(CAST(myColumn AS VARCHAR(8000)), 'ABC', '')) AS TEXT)

This casts the text to varchar, does the replacement, then casts it back to text.
0
 
SQL_SERVER_DBACommented:
select replace(columnname, 'string', 'withstrng')
0
 
misdevelopersAuthor Commented:
Hello,
Thank you for the suggestion.
When I tried it, I got:
Argument data type text is invalid for argument 1 of replace function.

The field is defined as a data type of text.

 

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.