replace and data type text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

replace and data type text

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-02-09 : 14:59:02

I have a table with a column (let's call it ColText) that's a data type field of text. This particular table has about 100 rows. Each cell for the ColText column has the underscore character in various places. Sometimes, a cell will look like this:

"The brown fox____over the lazy dog."

Or this:

"The brown hamster__over the lazy dog."

The point is the number of underscore characters in a arbitrary cell can range from one to many. My job is to the replace the underscore characters, regardless of its length, with one asterisk (*). So, for example, the result may look like this:

"The brown fox*over the lazy dog."

I found an article that describes a routine which can substitute for the replace function for data type text fields. But I need help with replacing the variable amount of underscores with one asterisk. I hope that makes sense.

Here is the article:
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

Any help would be appreciated.

Thank you very much in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 17:49:14

http://www.sqlteam.com/article/search-and-replace-in-a-text-column