RTF to TEXT in sql server

Question

I have a RTF field in my SQL 2005 table, I need to convert it to Text and display it. After a quick research I got the following method...

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}')

But Here I am getting only NULL as result... What could be the issue, please suggest

Answer 1

I have also same requirement but i will prefer not to use any richtext control dll or CLR in SQL Server. I do same thing by taking reference of System.Windows.Form.dll in my service / database layer project. Import that library at top of .cs file where we Import/Use our required library for that class and do below code. And it really resolved my problem. You can also try this and let me know if you face any problem.

STEPS To convert RTF To Text without RichTextControl ocx/dll or CLR in SQL Server

1) In your project Add Reference to System.Windows.Forms.dll (Preferably add this reference in ServiceLayer/DataBase Layer/ Domain Model Layer/Business Logic Layer)

2) Import/Use it in your class file where we import/use required namespace at the top of class

VB.Net --> Import Systems.Windows.Forms
C#.Net --> using System.Windows.Forms;

3) Add below code where you want to convert your RTF to Plain Text

 System.Windows.Forms.RichTextBox rtBox = new System.Windows.Forms.RichTextBox();
 rtBox.Rtf = actionNotes.Notes; // <your database table's RTF field data assigned to rtBox's Rtf property>
 string s = rtBox.Text; //This will convert your assigned RTF Field data in Plain Text

 return s;   //Return this string variable or assign it to the control where you want to display your data in Plain Text format