asked on

Remove formatting marks from data

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

ASKER CERTIFIED SOLUTION

QPR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QPR

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.

prairieits

ASKER

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

prairieits

ASKER

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

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

prairieits

ASKER

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

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.

prairieits

ASKER

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

when calling the function you mean?

maybe....
declare @theRTF varchar(2000)
select @theRTF = FieldName from theTable where A = B
select usrRTF2TXT(@theRTF)