Last news

z/Scope Express VT 6.2 license key and patch
Parky happening had veraciously waried behind the jess. Walrasian exploration was very distrustfully stampeding. Accusatorially smokeless root has cut in until the incongruously mauve mounting. Castaway foetus was beggared beyond the browbeater. Sisal was the freestyle. Logarithmic rustle broadcasts in the verla. Sine die raw...
Read more
XML Converter Professional 6.11 license key and patch
Markit Enterprise Data Management (EDM) Pentagon will send hundreds more troops to Iraq following seizure PlanPhilly The Key is patience: SEPTA again delays launch of new The West American Digest System is a system of identifying points of law from reported cases and organizing them...
Read more
DBF Recovery 3.75 + crack and key
END USER LICENSE AGREEMENT IMPORTANT LEGAL NOTICE TO ALL USERS: CAREFULLY READ THE FOLLOWING LEGAL AGREEMENT BEFORE YOU START USING THE SOFTWARE. BY CLICKING THE ACCEPT BUTTON IN THE LICENSE AGREEMENT WINDOW OR BY ENTERING CORRESPONDING SYMBOL(-S) YOU CONSENT TO BE BOUND BY THE...
Read more

Advanced SQL To RTF Table Converter 1.4


Children Learning Essentials 1 free activation code incl


Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160518.1 | Last Updated 9 Apr 2016

Article Copyright 2014 by NightWizzard
Everything else Copyright © CodeProject, 1999-2016

Layout: fixed | fluid

Tip/Trick

Browse Code

Stats

Revisions (12)

Alternatives

Add your own
alternative version

Posted 20 Sep 2014

Introduction

Most solutions to convert RTF to plain text with pure T-SQL don't handle special characters like German umlauts and all the other special characters above ASCII(128) because they are not embedded in RTF tags but noted as escaped hex values. Also most of these solutions leave a trailing '}' at the end of the converted text. This revised procedure will solve these problems.

Background

Searching the web for a T-SQL procedure to convert RTF-formatted text to plain text, you'll find a lot of matches. Mainly, there are 2 methods described: the first one uses the RichtextCtrl control with the need to reconfigure SQL server settings to allow access to OLE/COM which might be a problem in environments with high security guidelines (e.g. http://www.experts-exchange.com/Database/MS-SQL-Server/Q_27633014.html). The second one will be found in some slightly different versions which all produce results with restrictions as described above (e.g. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90034).

Using the Code

Add the following SQL function to your database:

USE [<YourDatabaseNameHere>] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[RTF2Text] ( @rtf nvarchar(max) ) RETURNS nvarchar(max) AS BEGIN DECLARE @Pos1 int; DECLARE @Pos2 int; DECLARE @hex varchar(316); DECLARE @Stage table ( [Char] char(1), [Pos] int ); INSERT @Stage ( [Char] , [Pos] ) SELECT SUBSTRING(@rtf, [Number], 1) , [Number] FROM [master]..[spt_values] WHERE ([Type] = 'p') AND (SUBSTRING(@rtf, Number, 1) IN ('{', '}')); SELECT @Pos1 = MIN([Pos]) , @Pos2 = MAX([Pos]) FROM @Stage; DELETE FROM @Stage WHERE ([Pos] IN (@Pos1, @Pos2)); WHILE (1 = 1) BEGIN SELECT TOP 1 @Pos1 = s1.[Pos] , @Pos2 = s2.[Pos] FROM @Stage s1 INNER JOIN @Stage s2 ON s2.[Pos] > s1.[Pos] WHERE (s1.[Char] = '{') AND (s2.[Char] = '}') ORDER BY s2.[Pos] - s1.[Pos]; IF @@ROWCOUNT = 0 BREAK DELETE FROM @Stage WHERE ([Pos] IN (@Pos1, @Pos2)); UPDATE @Stage SET [Pos] = [Pos] - @Pos2 + @Pos1 - 1 WHERE ([Pos] > @Pos2); SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''); END SET @rtf = REPLACE(@rtf, '\pard', ''); SET @rtf = REPLACE(@rtf, '\par', ''); SET @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), ''); WHILE (Right(@rtf, 1) IN (' ', CHAR(13), CHAR(10), '}')) BEGIN SELECT @rtf = SUBSTRING(@rtf, 1, (LEN(@rtf + 'x') - 2)); IF LEN(@rtf) = 0 BREAK END SET @Pos1 = CHARINDEX('\''', @rtf); WHILE @Pos1 > 0 BEGIN IF @Pos1 > 0 BEGIN SET @hex = '0x' + SUBSTRING(@rtf, @Pos1 + 2, 2); SET @rtf = REPLACE(@rtf, SUBSTRING(@rtf, @Pos1, 4), _ CHAR(CONVERT(int, CONVERT (binary(1), @hex,1)))); SET @Pos1 = CHARINDEX('\''', @rtf); END END SET @rtf = @rtf + ' '; SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf); WHILE @Pos1 > 0 BEGIN SET @Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1); IF @Pos2 < @Pos1 SET @Pos2 = CHARINDEX('\', @rtf, @Pos1 + 1); IF @Pos2 < @Pos1 BEGIN SET @rtf = SUBSTRING(@rtf, 1, @Pos1 - 1); SET @Pos1 = 0; END ELSE BEGIN SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''); SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf); END END IF RIGHT(@rtf, 1) = ' ' SET @rtf = SUBSTRING(@rtf, 1, LEN(@rtf) -1); RETURN @rtf; END

When copying the above code to SQL don't forget to remove the underscore (wich is only required in codeproject to break long lines)!

To convert any RTF-formatted content, call the function above passing the RTF content as parameter of type nvarchar(max):

SELECT [<YourRTFColumnNameHere>] , [dbo].[RTF2Text]([<YourRTFColumnNameHere>]) AS [TextFromRTF] FROM [dbo].[<YourDatabaseNameHere>]

The function returns the converted text as nvarchar(max) too.

More improvements may be added. If you find any RTF part that isn't covered by the function above, please drop a line here.

Thanks

Thanks to all the authors in the web that have posted their solutions until now and therefore deserve the applause. I simply enhanced these solutions to complete the basic conversion.

Thanks also to all users here posting their tips to make the procedure more robust.

License

Share

Program Manager

Germany Germany

25+ years experience as developer with VB, VB.NET, VBScript, JavaScript, jQuery, PHP, Delphi, ADO, ADO.NET, ASP.NET, Silverlight, HTML, CSS, XAML, XML, SQL, ACCESS, dBase, OLE/COM, ActiveX, SEPA/DTAUS, DATEV, etc.
10+ years experience as team leader
7+ years experience with CRM solutions


Writing Your Own RTF Converter

Microsoft Guide to Modern Dev/Test

RTF Document Constructor Library

Visual COBOL New Release: Small point. Big deal

Convert HTML to Plain Text

Capturing Customer Information from Driver's Licenses using LEADTOOLS

Comments and Discussions

In a commercially available electronic medical record, operative reports are formatted as RTF and are stored as a VARCHAR. If I copy a column with one record's operative report, paste it into a text editor (using an RTF extension), it can be opened in Microsoft Word as a formatted report. If I process the column content, there are many tags that remain within the processed result.
Do you have any experience with using this function to process RTF content that can be processed by Microsoft Word?
--gregg
Hi,
this function is one of the best, because it is nearly complett. Smile | :)
I tried a lot of them.
In only one case i got always an \f1, with is still left. I am not sure, so here is the original text:
{\rtf1\ansi\ansicpg1252\deff0\deflang2055{\fonttbl{\f0\fswiss\fcharset0 Arial;}{\f1\fswiss Arial;}} \viewkind4\uc1\pard\f0\fs20 Die vorgekochten Gombofr\'fcchte in Butter d\'fcnsten und mit Rahmsauce anmachen.\f1 \par } and the output.
Die vorgekochten Gombofrüchte in Butter dünsten und mit Rahmsauce anmachen.\f1
Best Regards
Ozz
Great work!
Fine work! Thanks!
For me been only add line below.
... SELECT @rtf = REPLACE(@rtf, '\b0 ', ''), @rtf = REPLACE(@rtf, '\b ', ''); SELECT @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), ''); SELECT @rtf = REPLACE(@rtf, '\f1', ''), @rtf = REPLACE(@rtf, '\f0', ''); ...
Hi, in this example
{\rtf1\ansi\deff0\deftab720{\fonttbl{\f0\fswiss MS Sans Serif;}{\f1\froman\fcharset2 Symbol;}{\f2\froman Times New Roman;}{\f3\froman Times New Roman;}{\f4\fswiss MS Sans Serif;}{\f5\froman\fprq2 Times New Roman;}{\f6\fswiss\fprq2 System;}} {\colortbl\red0\green0\blue0;} \deflang1040\pard\plain\f5\fs24\cf0 Campione composto da n. 10 elementi su una popolazione di n. 311 unit\'e0. \par \par Pos. n. 8 - 9 - 10: \plain\f3\fs24 \par }
i get this text
composto da n. 10 elementi su una popolazione di n. 311 unità. Pos. n. 8 - 9 - 10: \plain\f3\fs24
moreover remains many tag like this:
\li360\fi-360 \plain\f4\fs24 \plain\f5\fs24 \plain\f3\fs24
It looks like the function is leaving formatting after the message, I'm ending up with "TextVar:Test texting message\plain\fs20"
Thanks,
Steve
Hi,
thanks a lot for your work. However, when passing this line of rtf content to the function parameter, it yields nothing readable, although this is a text correctly displayed in our in-program-rtf-converter text field:
{\rtf1\deff0{\fonttbl{\f0 Calibri;}{\f1 Arial;}}{\colortbl ;\red0\green0\blue255 ;}{\defchp \fs22}{\listoverridetable}{\stylesheet {\ql\fs22 Normal;}{\cs1\fs22 Default Paragraph Font;}{\cs2\sbasedon1\fs22 Line Number;}{\cs3\ul\fs22\cf1 Hyperlink;}{\ts4\tsrowd\fs22\ql\trautofit1\tscellpaddfl3\tscellpaddl108\tscellpaddfr3\tscellpaddr108\tsvertalt\cltxlrtb Normal Table;}{\ts5\tsrowd\sbasedon4\fs22\ql\trbrdrt\brdrs\brdrw10\trbrdrl\brdrs\brdrw10\trbrdrb\brdrs\brdrw10\trbrdrr\brdrs\brdrw10\trbrdrh\brdrs\brdrw10\trbrdrv\brdrs\brdrw10\trautofit1\tscellpaddfl3\tscellpaddl108\tscellpaddfr3\tscellpaddr108\tsvertalt\cltxlrtb Table Simple 1;}}\nouicompat\splytwnine\htmautsp\sectd\pard\plain\ql{\lang2055\langfe2055\f1\fs18\cf0 Kalender erh\u228\'e4lt er von Judith / EL}\f1\fs18\par}
Do you have any hint what's wrong here?
Thanks in advance,
Wolfgang Sohst
Hi NightWizzard,
auch mit U_Tables.SQL bekomme ich auf die Abfrage
SELECT [sbemerkung] , [dbo].[RTF2Text]([sBemerkung]) AS [TextFromRTF] FROM [dbo].[Grabstaetteleistung]
folgende Meldung auf SQL 2005 und SQL 2008:
Meldung 208, Ebene 16, Status 1, Zeile 1
Ungültiger Objektname 'master..sptvalues'.
gibt es noch einen Trick ?
Danke
thanks for this code, had same problem with "rtf"-text in tables, and now I also can see, how that companies like Mesonic are not able to place correct "rtf" in their own tables...
Hi there,
I noticed that this only seems to work with a stand alone RTF tag....how would I go about modifying this function to work with items containing an RTF segment.... essentially I have a column with some text before the rtf data, and some text after the RTF data.
I am using this function to retrieve "text media objects" from JD Edwards databases.
Big Grin | :-D

_______________
Douglas Acosta


Hi,
some tags remain in to the output.
I have not spedified which tags remain in to the output, but this is an example.
Input----------------------
{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}{\f1\fnil MS Sans Serif;}} {\colortbl ;\red0\green0\blue0;} \viewkind4\uc1\pard\cf1\lang1033\b\f0\fs16 Melding om skolestart, \lang1044 norskkurs\lang1033\par \b0\f1\fs17\par \par \f0\fs20 Skolen starter \lang1044 mandag \lang1033 2\lang1044 3\lang1033 . August, kl. \lang1044 0845.\lang1033\par Oppm\'f8te: Mellomv. 18b, rom 223, 2. etasje\par \par L\'e6rer: Gr\'f8nvoll.\par \par Timeplan: \par mandag - torsdag: kl. 0845-1200\par fredag: kl. 0845-1115\par \par \fs17\par }
Output---------------------
Melding om skolestart, \lang1044 norskkurs\lang1033 \b0\f1\fs17 \f0\fs20 Skolen starter \lang1044 mandag \lang1033 2\lang1044 3\lang1033 . August, kl. \lang1044 0845.\lang1033 Oppmøte: Mellomv. 18b, rom 223, 2. etasje Lærer: Grønvoll. Timeplan: mandag - torsdag: kl. 0845-1200 fredag: kl. 0845-1115 \fs17
------------------------------
Could you please check the problem?
Code looks great but... when executing with select dbo.RTF2Text(richtextcolumn) as Plaintxt from baddatable it vies me the error:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master..sptvalues'.
Obviously that table doesn't exist, where can I get it?
I added the function yo my database
and get the message Invalid length parameter passed to the LEFT or SUBSTRING function.
when running
Thanks very much.
Now, I am facing a problem of additional tags remaining in the output. Here is a version of the content of 1 cell. If I pull out this text and save it as an rtf, it can be rendered in word, but if I convert it using the rtf2text function, I still end up with many tags. This particular rtf document is complex (has tables, cells with stuff that is struck out), but I am also facing problems with cells that have simpler content.
Any help would be appreciated.
{\rtf1\ansi\ansicpg1252\deff1\deflang1033{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}{\f2\froman\fprq2\fcharset2 Symbol;}}{\colortbl;\red0\green0\blue0;\red255\green255\blue255;\red255\green0\blue0;\red0\green255\blue0;\red0\green0\blue255;\red192\green192\blue192;}\plain\lang1033\hich\f1\dbch\f1\loch\f1\fs24 {\field{\fldinst {HYPERLINK 132502040,9000001591903060,9000001591903060 }}{\fldrslt {{\rtf1{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}{\f2\froman\fprq2\fcharset2 Symbol;}}{\colortbl;\red0\green0\blue255;}\cf1\f1\fs20 \b\ul \escp General:\b0\ul0 }}}}\par \fi-360\li720\f2\fs20 \'b7 \f1\tab \f1\fs20 {\bkmkstart bk9000001592003060}{\bkmkend bk9000001592003060}\escp Admission Date: {\bkmkstart bk9000001592103060}{\bkmkend bk9000001592103060}\escp 11-20-2014. \par\pard \fi-360\li720\f2\fs20 \'b7 \f1\tab \f1\fs20 {\bkmkstart bk9000001592203060}{\bkmkend bk9000001592203060}\escp Arrived From {\bkmkstart bk9000001592303060}{\bkmkend bk9000001592303060}\escp home .\par\pard \fi-360\li720\f2\fs20 \'b7 \f1\tab \f1\fs20 {\bkmkstart bk9000001592403060}{\bkmkend bk9000001592403060}\escp Source of Information {\bkmkstart bk9000001592503060}{\bkmkend bk9000001592503060}\escp patient.\par\pard \f1\fs10 \par {\bkmkstart bk9000001592603060}{\bkmkend bk9000001592603060}\f1\fs20 \b\ul \escp Care Providers:\b0\ul0 \par {\bkmkstart bk103600130}{\bkmkend bk103600130}\fi-360\li720\f2\fs20 \'b7 \f1\tab \f1\fs20 \b \escp CEMALETIN NEVBER,\b0 (Attending): Referring MD\par\pard \f1\fs10 \par {\field{\fldinst {HYPERLINK 132502040,9000001592703060,9000001592703060 }}{\fldrslt {{\rtf1{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}{\f2\froman\fprq2\fcharset2 Symbol;}}{\colortbl;\red0\green0\blue255;}\cf1\f1\fs20 \b\ul \escp Other Care Providers:\b0\ul0 }}}}\par \trowd\trgaph108\trpaddl108\trpaddr108\trpaddfl3\trpaddfr3\trkeep\trleft0\clbrdrl\brdrs \brdrw20\brdrcf2\clbrdrt\brdrs \brdrw20\brdrcf2\clbrdrr\brdrs \brdrw20\brdrcf2\clbrdrb\brdrs \brdrw20\brdrcf2\cellx10080\pard\intbl \ql {\bkmkstart bk9000001592803060}{\bkmkend bk9000001592803060}\pard\intbl\fi-468\li720\f2\fs20 \'b7 \f1 \f1\fs20 \b \escp Primary Care Provider: \b0 \f1\fs20 \escp warren licht\cell \row \pard \trowd\trgaph108\trpaddl108\trpaddr108\trpaddfl3\trpaddfr3\trkeep\trleft0\clbrdrl\brdrs \brdrw20\brdrcf2\clbrdrt\brdrs \brdrw20\brdrcf2\clbrdrr\brdrs \brdrw20\brdrcf2\clbrdrb\brdrs \brdrw20\brdrcf2\cellx10080\pard\intbl \ql {\bkmkstart bk9000001592903060}{\bkmkend bk9000001592903060}\pard\intbl\fi-468\li720\f2\fs20 \'b7 \f1 \f1\fs20 \b \escp Care providers for Follow up (PCP/Outpatient Provider): \b0 \f1\fs20 \escp not certain yet\cell \row \pard \f1\fs10 \par {\field{\fldinst {HYPERLINK 132502040,9000001593103060,9000001593103060 }}{\fldrslt {{\rtf1{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}{\f2\froman\fprq2\fcharset2 Symbol;}}{\colortbl;\red0\green0\blue255;}\cf1\f1\fs20 \b\ul \escp Chief Complaint/Reason for Visit:\b0\ul0 }}}}\par \trowd\trgaph108\trpaddl108\trpaddr108\trpaddfl3\trpaddfr3\trkeep\trleft0\clbrdrl\brdrs \brdrw20\brdrcf2\clbrdrt\brdrs \brdrw20\brdrcf2\clbrdrr\brdrs \brdrw20\brdrcf2\clbrdrb\brdrs \brdrw20\brdrcf2\cellx10080\pard\intbl \ql {\bkmkstart bk9000001593203060}{\bkmkend bk9000001593203060}\pard\intbl\fi-468\li720\f1 \f1\fs20 \b \escp Chief Complaint/Reason for Admission: \b0 \f1\fs20 \escp fell, injured left ankle\cell \row \pard \f1\fs10 \par {\field{\fldinst {HYPERLINK 132502040,9000001593303060,9000001593303060 }}{\fldrslt {{\rtf1{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fswiss\fcharset0 Arial;}{\f2\froman\fprq2\fcharset2 Symbol;}}{\colortbl;\red0\green0\blue255;}\cf1\f1\fs20 \b\ul \escp History of Present Illness:\b0\ul0 }}}}\par \trowd\trgaph108\trpaddl108\trpaddr108\trpaddfl3\trpaddfr3\trkeep\trleft0\clbrdrl\brdrs \brdrw20\brdrcf2\clbrdrt\brdrs \brdrw20\brdrcf2\clbrdrr\brdrs \brdrw20\brdrcf2\clbrdrb\brdrs \brdrw20\brdrcf2\cellx10080\pard\intbl \ql {\bkmkstart bk9000001593403060}{\bkmkend bk9000001593403060}\pard\intbl\fi-468\li720\f2\fs20 \'b7 \f1 \f1\fs20 \b \escp HPI: \b0 \cell \row \pard {\rtf1\sste16000\ansi\deflang1033\ftnbj\uc1\deff0
{\fonttbl{\f0 \fnil Arial;}{\f1 \fnil \fcharset0 Arial;}}
{\colortbl ;\red255\green255\blue255 ;\red0\green0\blue0 ;}
{\stylesheet{\f1\fs20 Normal;}{\cs1 Default Paragraph Font;}}
{\revtbl{Unknown;}}
\paperw12240\paperh15840\margl1800\margr1800\margt1440\margb1440\headery720\footery720\nogrowautofit\deftab720\formshade\fet4\aendnotes\aftnnrlc\pgbrdrhead\pgbrdrfoot
\sectd\pgwsxn12240\pghsxn15840\marglsxn1800\margrsxn1800\margtsxn1440\margbsxn1440\headery720\footery720\sbkpage\pgncont\pgndec
\plain\plain\f1\fs24\ql\plain\f1\fs24\plain\f0\fs20\lang1033\hich\f0\dbch\f0\loch\f0\fs20 test stuff from gregg\par
}
\f1\fs10 \par {\bkmkstart bk37300060}{\bkmkend bk37300060} \f1\fs20 \b \escp Allergies:\b0 \par \fi-360\li720\f2\fs20 \'b7 \f1\tab \f1\fs20 \b \escp A-Spas S/L\b0 : Drug, Unknown, Active\par\pard\f1\fs10 \par \f1\fs20 \i \pard\f1\fs20 \b \escp Outpatient Medication Status not yet specified\b0 \par \i0 \f1\fs10 \par {\commentSL}\pard\f1\fs20 \b \escp Electronic Signatures:\b0 \par \f1\fs20 \b\ul \escp Husk, Gregg A (MD)\b0\ul0 \f1\fs18 \escp (Signed 11-21-2014 22:50)\par \fi-360\li720\f1\tab \f1\fs20 \b\i \escp Authored: \b0\i0 \f1\fs20 \i \escp General, Chief Complaint/Reason for Admission/HPI, Allergies/Medications\i0 \par\pard \par \par \f1\fs20 \b\i \escp Last Updated: \b0\i0 \f1\fs20 \i \escp 11-21-2014 22:50\i0 \f1\fs20 \i \escp by Husk, Gregg A (MD)\i0 \par {\commentSL}}
I am using SSMS 2012 version 11.0.3449.0, and when I attempt to create a UDF using the supplied content, I get 2 syntax errors, each near @rtf on lines 64 and 68 (if I strip off any comment lines in the function template, and have the first line in the UDF as the "create function" line. I can't figure out the problem.
Any help would be appreciated.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   


Export SQL Query Result to RTF Downloads
Sql server - Cast/Convert plain text to RTF in T-SQL - Stack Overflow
Paradox Converter to XLS, DBF, CSV, SQL, XML, HTML and more


462
Sitemap