Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

convert code to plain txt

Status
Not open for further replies.
yuchieh

yuchieh

MIS
Jul 21, 2000
178
US
I have data like this in SQL (server 2000) --

<p class="MsoNormal" style="margin: 0in 0in 0pt"><b style="mso-bidi-font-weight: normal"><span style="font-size: 14pt; mso-bidi-font-size: 10.0pt"><font face="Times New Roman">Summary of Experience<o:p></o:p></font></span></b></p>

Is there a way in the query that I can convert to plain txt to display?

thanks
 
Sort by date Sort by votes
There was a similar question a while ago here:

thread183-1591754

The user defined function provided in that thread will work with SQL2000, but only if you are NOT using the text/ntext data type. Anyway... take a look and let us know how you make out.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hi,

unfortunately, those fields are using text datatype in the database. I did create the function using varchar. then when I did select to call one of the fields, it took forever to run the query and nothing came up. so I killed it.

any other way?

thanks
 
Upvote 0 Downvote
How many records are you talking about.... That might take a while to run.

Simi

 
Upvote 0 Downvote
Can you post the data in the column for that one record?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
<html>&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;b style=&quot;mso-bidi-font-weight: normal&quot;&gt;&lt;span style=&quot;font-size: 14pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Summary of Experience&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/b&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;span style=&quot;font-size: 14pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&amp;nbsp;&lt;/font&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Ethics training and consultation&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Clinical and administrative supervision of professionals, graduate students and volunteers&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Family, couple and individual therapy&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Therapeutic, recreational, socialization and psycho-educational groups&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Program administration, development and community relations&lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.25in; margin: 0in 0in 0pt 0.25in; tab-stops: list .25in; mso-list: l4 level1 lfo1&quot;&gt;&lt;span style=&quot;font-family: Symbol; font-size: 12pt; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;&amp;middot;&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;Expertise in the areas of trauma, addictions, gender and mediation &lt;o:p&gt;&lt;/o:p&gt;&lt;/font&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&amp;nbsp;&lt;/font&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&amp;nbsp;&lt;/font&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&lt;b style=&quot;mso-bidi-font-weight: normal&quot;&gt;&lt;span style=&quot;font-size: 14pt; mso-bidi-font-size: 10.0pt&quot;&gt;Education&lt;/span&gt;&lt;/b&gt;&lt;span style=&quot;font-size: 14pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/font&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;span style=&quot;font-size: 14pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&amp;nbsp;&lt;/font&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.5in; margin: 0in 0in 0pt 0.5in; tab-stops: list .5in; mso-list: l0 level1 lfo2&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&lt;i style=&quot;mso-bidi-font-style: normal&quot;&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;span style=&quot;mso-list: Ignore&quot;&gt;1988&lt;span style=&quot;font: 7pt &amp;quot;Times New Roman&amp;quot;&quot;&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;/i&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;The Family Institute of Chicago at &lt;st1:place w:st=&quot;on&quot;&gt;&lt;st1:placename w:st=&quot;on&quot;&gt;Northwestern&lt;/st1:placename&gt; &lt;st1:placetype w:st=&quot;on&quot;&gt;University&lt;/st1:placetype&gt;&lt;/st1:place&gt;: two year post-graduate training program in family therapy&lt;o:p&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/font&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;margin: 0in 0in 0pt&quot;&gt;&lt;span style=&quot;font-size: 12pt; mso-bidi-font-size: 10.0pt&quot;&gt;&lt;o:p&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&amp;nbsp;&lt;/font&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot; style=&quot;text-indent: -0.5in; margin: 0in 0in 0pt 0.5in; tab-stops: list .5in; mso-list: l1 level1 lfo3&quot;&gt;&lt;font face=&quot;Times New Roman&quot;&gt;&lt;i style=&quot;mso-bidi-font-style: normal&quot;&gt;&lt;span style=&q
 
Upvote 0 Downvote
I ran the function on your data, but it seems like there are issues. <sigh> Since you are using SQL2000 and the text data type, and your data is larger than 8000 characters, this function isn't going to work for you. I mean... the function was written for sql2005 (because it uses the varchar(max) data type). with SQL2000, you are limited to varchar(8000) or nvarchar(4000). The data you just posted is longer than 8000 characters.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
yeah, I figured. any other possible solutions? Thanks for testing it for me.
 
Upvote 0 Downvote
This is tricky. There are a couple ways to "solve" this problem, but you're probably not going to like any of them.

1. Upgrade to SQL2005, SQL2008, or SQL2008R2. Your database engine is 10 years old. Upgrading to a newer version is not an unreasonable suggestion. Once you are using a newer version of SQL server, you can use the varchar(max) data type to accomplish this.

2. Create another column in your table to store the "clean" version of this data. Then, write an application using whatever language you are comfortable with. This application would load the HTML data, remove the tags, and then save back to the table.

With the multiple column approach, there is always a possibility that the clean version of the data no longer matches the original. To prevent this, you could write an update trigger that clears out the data in the "clean" column (setting it to NULL).

3. Change your front end where you display the data to do the clean up for you. This is likely to mean that the code will be run more often, but you won't have to worry about the data getting out of sync.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hi George,

thanks for all the ideas. out of the sudden, our web developer found the solution on his end so he fixed the data and the forms. if he could have implemented this in the beginning,... dauh...

anyway. we are good. again, thank you for all the replied.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
728
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
1K
mikrom
mikrom
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
842
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question
How to import plain text from Word doc to SQL 1
Replies
4
Views
248
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
535
Volvere
V

Part and Inventory Search

Sponsor

Back
Top