RTF to TEXT Conversion

Hello, It's urgent for me....
I got a table in SQL Server database, in which a field of datatype 'text' contains an rtf string. I've to extract this data as a text using a function/any thing. can anyone suggest me, how can I go through the conversion?
Please help me.
thank you.
rakesh830Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
what does this "RTF text" look like ?
can you post a sample ?
0
SashPCommented:
Hi rakesh830

To parse RTF text you are going to need a library to parse it.  Forget writing a parser in TSQL.

You could (although I would not recommend it) use the richtx32.ocx which exposes the RichTextLib.RichTextBox Control.

Using OLE Automation you should be able to create this control populate it with the RichText then read the text from the control.

Even writing this responce makes me cringe as it sounds like a bad idea to me, but it "should" work even if performance is an issue.

Cheers Sash
0
rakesh830Author Commented:
the sample looks like this:

{\rtf1\ansi\ansicpg1252\deff0\deflang1031{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}
{\colortbl ;\red0\green0\blue0;\red0\green0\blue255;}
\viewkind4\uc1\pard\cf1\f0\fs20 abc, xyz
\par xyz
\par email: \cf2\ul xyz@xy.com\cf1\ulnone
\par
\par abc, xyz, abc
\par abc\cf0
\par }

Please tell me How can I get it converted into text.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sigmaconCommented:
What programming language are you using to access the DB? (for Hilaire:) RTF stands for Rich Text Format. Sql Server itself is not going to parse RTF for you, it will involve some external solution. If you're in any windows-based environment, take SashP's solution. If you are using Java, Swing has a thing (I think called RTFEditorKit) that could do it for you. Ask in the forums particular for the language you are using.
0
SashPCommented:
How does the data end up in the database? how do you want the data retrieved to be accessed?
Do you need to search the result or just display it to a user?

If you need to display it in an application then use the RichTextBox control from the richtxt32.ocx

0
rakesh830Author Commented:
I need to transform whole table into text. my table contains columns like id, name and this rtf. How can I change my table to display this rtf to Text.
0
HilaireCommented:
>>RTF stands for Rich Text Format.<<
I knew that but thought there was no official specification. I was wrong. Thanks anyway.

I tend to use regular expressions in SQL (sp_oa* methods along with VBScript.Regexp) whenever I can to deal with formatted text, tags, ...

In this case it's too complicated.
I think you'll have to use the ocx as suggested by SashP.
0
rdrunnerCommented:
I would also suggest using the richtextbox...

You can assign the contens of the textfield into the rtf property and extract the stripped text from the text property

Like this

RichTextBox1.TextRTF = rs("MyRTFField")
msgbox (RichTextBox1.Text )

This is quite simple but you cant do it on an sql server... (Actually you can but i wouldnt suggest it)
0
rakesh830Author Commented:
can I use any Functions or procedures for extracting the data as Text, in views or in other. can you please post me a sample code if u have.
0
ispalenyCommented:
create function dbo.RTF2TXT(@in varchar(8000)) RETURNS  varchar(8000) AS
BEGIN

DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
return @out

END
GO

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rakesh830Author Commented:
Thank you all....
and Thank you very much Mr. Spaleny. It's working with the function that u gave. But, I got a small problem with this. When the RTF is converted, there are some charecters like squares at the end of every line where there is a next line. How can I remove them?
Thank You for your time and effort.
0
Ken SelviaRetiredCommented:
Nice ispaleny!
0
SashPCommented:
rakesh830,

The characters at the end of the lines will be the Carriage Return and Line Feed Characters.  char(13) and char(10).

You can replace them with empty strings '' using replace([Your Column],char(13)+char(10),'')

You also may come across the char(9) tab character in you text.

If you are putting the RTF2TXT function onto a production server make sure that you performance test the code extensively, I would be concerned with memory leaks, hung connections that you may not be able to kill and performance problems for other databases.

Also there could be security issues as the access to activex functions on a SQL Server is a security risk.

Cheers Sash
0
Ken SelviaRetiredCommented:
Ispaleny your idea looked interesting so I wanted to try it.  On Windows 2003 server I get "Class is not licensed for use" (I added some error trapping to see why it was failing)

I see it is installed with various MS OS's http://msdn.microsoft.com/library/default.asp?url=/library/en-us/shellcc/platform/commctls/RichEdit/RichEditControls.asp and even though Win2003 is not listed, the msftedit.dll is in my C:\Windows\system32 directory.

Has anyone else has used this com object on Win2003, or Win2K for that matter?
0
rakesh830Author Commented:
Thank you for your replies.
Thank You very much for your suggestions. I got Solution for my problem. Thank u verymuch.
0
ispalenyCommented:
kselvia,

you need to install and register richtx32.ocx file.
0
SISDaliaCommented:
I have this same problem.  I have a varchar(max) field in my SQL Server database table.  My field has RTF characters that I need to strip out so that I can display the text only.

My SQL Server 2008 is on a windows 2003 server.

I created the function above:

create function dbo.RTF2TXT(@in varchar(8000)) RETURNS  varchar(8000) AS
BEGIN

DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
return @out

END
GO


I also downloaded Richtx32.ocx and registered it using
regsvr32 c:\windows\system32\Richtx32.ocx  (got a message that it registered successfully).

However when I test the function using

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')

I get a null.

Can you please tell me how to get this to work.

Thanks,
SISDalia
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.