We help IT Professionals succeed at work.

Remove formatting marks from data

prairieits
prairieits asked
on
326 Views
Last Modified: 2008-01-09
I have a SQL 2k field which contains the following typical data:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}{\f1\fnil MS Sans Serif;}}  \viewkind4\uc1\pard\f0\fs24 This is the good data.  This is good data. \par\par This is more good data  \f1\fs16   \par }  

Best case, I want it to return the following:

This is good data.  This is good data.

This is good data.

If I can't get the paragraphs to display that way, this would do as well:

This is good data. This is good data. This is good data.

Thanks in advance,
Jerod



Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
QPR

Commented:
Just tested it.....

Created the UDF then run....

declare @theRTF varchar(200)
set @theRTF = '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}{\f1\fnil MS Sans Serif;}}  \viewkind4\uc1\pard\f0\fs24 This is the good data.  This is good data. \par\par This is more good data  \f1\fs16   \par } '

select dbo.usrRTF2TXT(@theRTF)
and it returns This is the good data.  This is good data.
It misses out "this is more good data" you may have to tweak to suit unless someone comes up with a better solution.

Where is this RTF destined for? Some applications (e.g. Crystal reports) have the ability to show RTF as text only if you set the properties of the text box/label etc to do so.

Author

Commented:
It is headed to a Coldfusion Application.  I have to admit that your UDF looks great but that I don't know UDF's well enough to know exactly what it is doing.  I'll spend some time with it and just work my way through.

Cool idea on the formatting.  I'll play with it to see if I can get it to automagically take care of it another way.  

Ultimately, I would like to setup a nightly job that creates a copy of the raw data and then cleans up the formatting so that if something goes bad, it isn't on the production data.

Thanks,
Jerod

Author

Commented:

Well, I got it to work in Crystal by just changing the text interpretation to RTF.  It pulled out the /par's and everything.

Now to just find a similar function in SQL... :)

Thanks,
Jerod
QPR

Commented:
No such thing, you have to write your own or do it at the application level

Author

Commented:
Dang.  You'd think if Crystal could do it, there would be some sort of convert or cast statement that could have been done to easily parse out the useful data.

Thanks for the help.  I'll get started on your UDF.

-Jerod
QPR

Commented:
Yes you would, and we searched and scratched our head and a colleague ended up writing the above.
We are using SQL Reporting Services 2000 and (this version) doesn't have the same ability as Crystal hence writing our own fix.

Author

Commented:
I got it to work using my example.  If I try to replace the string value with a field name, I get a syntax error.  Sorry for the likely dumb question but what do I need to do with the syntax in order for the function to pull the data from a sql field?

Thanks,
Jerod
QPR

Commented:
when calling the function you mean?

maybe....
declare @theRTF varchar(2000)
select @theRTF = FieldName from theTable where A = B
select usrRTF2TXT(@theRTF)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.