December 20, 2005 at 8:27 am
Hi all,
question: I have text data, containing rtf tags. I need to strip it and display it.(it goes to CR). I figured, easy way to do it would be to copy the text to the Win clipboard and paste it back to a variable.
Anyone knows how to do it in SQL Server (as a function, api call or similar ?)
Thanx
peter
December 20, 2005 at 10:05 am
Well, I'm not quite sure what you mean "do it in SQL Server". Do you have RTF text stored in a field and are trying to turn it into plain text? Or are you trying to turn RTF text in the clipboard to plain text before pasting into SQL Server? The second question seems to relate more to the application you're using.
Regardless, here's one (although kludgy) way to do it from C#. You create an instance of a RichTextBox control, set the RTF property of the control, then read the Text property. Something like:
Public string Rtf2Text(string RtfText) { System.Windows.Forms.RichTextBox richtextbox1 = new System.Windows.Forms.RichTextBox(); richtextbox1.Rtf = RtfText; Return richtextbox1.Text; }
I suppose you could publish this function to you SQL server and then use it to convert any RTF text (input through a stored procedure or caught by a trigger) to text.
JR
December 20, 2005 at 11:47 am
Thank you, well, 'do it in SQL Server' meant a call from a stored proc.
I'm using this:
function dbo.st_RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)
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
but it's library dependent (rich32txt.ocx) and some older versions don't work the way I want. I meant using Windows Clipboard call instead richtextbox.
peter
December 20, 2005 at 1:20 pm
Sorry, thought you were in SQL 2005.
I think it can be done with Windows API calls to OpenClipboard (e.g. http://www.mentalis.org/apilist/OpenClipboard.shtml) and then calling SetClipboardData and GetClipboardData with different formats. But, I have no idea how to make an API call like that from TSQL, and I'm not sure if you can access the clipboard from a non-UI login.
JR
December 20, 2005 at 2:42 pm
I wrote a small dll, which takes a string runs it through the clipboard and spits it out. Then I created a extended proc tied to the dll and now it says 'Invalid object name' when I call the proc. Pain in the a..
thnx though
p.
December 21, 2005 at 5:24 am
Hi, I'm new to this SQL stuff so I feel a little out of my depth ... I hope this is not stupid!!
I had a similar problem stipping RTF tags from a number of my records for an Excel Report. My solution was to install xp_regex and create a function to use a regular Expression:
EXEC master.dbo.xp_regex_replace @MyRTFText, '({\\)(.+?)(})|(\\)(.+?)(\b)|}|\s?', '', @MyText OUTPUT
see http://www.codeproject.com/managedcpp/xpregex.asp
I have now called this function from a number of procedures covering a range of tables and so far.... no problems.
December 21, 2005 at 8:21 am
hey,
thanx, but your regex is dropping all spaces in the text
December 21, 2005 at 9:00 am
I believe you need to change the regex by removing the |\s? at the end. That looks like a blanket search for whitespace. So it would look like:
'({\\)(.+?)(})|(\\)(.+?)(\b)}'
December 21, 2005 at 9:12 am
I know, but something's still not right with the regex, it drops some records, which are clearly rtf text
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply