[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

RTF to TEXT Conversion

Posted on 2004-11-03
17
Medium Priority
?
7,240 Views
Last Modified: 2013-01-23
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.
0
Comment
Question by:rakesh830
  • 5
  • 3
  • 2
  • +5
17 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12483080
what does this "RTF text" look like ?
can you post a sample ?
0
 
LVL 8

Expert Comment

by:SashP
ID: 12483134
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
 

Author Comment

by:rakesh830
ID: 12483168
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:sigmacon
ID: 12483188
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
 
LVL 8

Expert Comment

by:SashP
ID: 12483195
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
 

Author Comment

by:rakesh830
ID: 12483229
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12483246
>>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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12483305
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
 

Author Comment

by:rakesh830
ID: 12483312
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
 
LVL 13

Accepted Solution

by:
ispaleny earned 2000 total points
ID: 12483602
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
 

Author Comment

by:rakesh830
ID: 12485015
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
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12485103
Nice ispaleny!
0
 
LVL 8

Expert Comment

by:SashP
ID: 12487246
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
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12487531
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
 

Author Comment

by:rakesh830
ID: 12491579
Thank you for your replies.
Thank You very much for your suggestions. I got Solution for my problem. Thank u verymuch.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 12493265
kselvia,

you need to install and register richtx32.ocx file.
0
 

Expert Comment

by:SISDalia
ID: 37722932
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question