adding a new line

Hello,

I have a column in my table that is ntext data type and it has html data. I need to strip all the html tags, which is pretty easy to do, but I also need to add a new line for every instance of "

". Do any of you know of a function or anything else that does that?

<p style="margin: 0px">11/21/06 - Position closed and filled by competition.

<p style="margin: 0px">&nbsp;

<p style="margin: 0px">11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position. Please respond NEW POSITION FOR ETS Location: Princeton, NJ Duration: 6 months New Max Rate: $55.00 Recruiter: Steve McDowell

If you are using SQL Server 2005 then convert the column to nvarchar(max). You can then use any of the built-in string functions to replace the values. However, these are operations that is not meant for SQL Server. It is more appropriate to clean the data on the client side before saving it to the database. If you are using SQL Server 2000 then you can use PATINDEX to search for string

and replace it. Below are some scripts that will help.

http://www.umachandar.com/technical/SQL6x70Scripts/Main62.htm -- strips html tags from varchar field. You have to modify it a bit to use it with ntext

http://www.umachandar.com/technical/SQL6x70Scripts/Main53.htm -- shows how to use updatetext

|||

Hello Uma,

First of, Thanks for your help!

I went through the code and I still dont understand how the code will add a new line for

or <n></n> instances. When you look at the data using IE you will see the columns appears like this:

11/21/06 - Position closed and filled by competition.

11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position.

But in my table you see the column appears with all the html tags and comes out like this:

<p style="margin: 0px">11/21/06 - Position closed and filled by competition.

<p style="margin: 0px">&nbsp;

<p style="margin: 0px">11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position.

I need to clean up the data and make it appear as simple text in my table without all the html tags but when that column (the new col) is view using IE, it needs to still show the new line for every

or <n></n>.

Can you help? Please let me know if you have any questions.