[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove a  string in a text field

Posted on 2007-10-16
3
Medium Priority
?
1,663 Views
Last Modified: 2012-06-27
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,

0
Comment
Question by:misdevelopers
3 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20089093
select replace(columnname, 'string', 'withstrng')
0
 

Author Comment

by:misdevelopers
ID: 20089172
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
 
LVL 15

Accepted Solution

by:
dbbishop earned 1000 total points
ID: 20089531
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question