Click here to Skip to main content
15,355,050 members
Articles / Mobile Apps / Windows Mobile
Posted 21 Sep 2014


15 bookmarked

Convert RTF to Plain Text (Revised Again)

Rate me:
Please Sign up or sign in to vote.
4.91/5 (15 votes)
9 Apr 2016CPOL1 min read
Handling for hex expressions and the trailing '}'


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.


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. The second one will be found in some slightly different versions which all produce results with restrictions as described above (e.g.

Using the Code

Add the following SQL function to your database:

USE [<YourDatabaseNameHere>]


    @rtf nvarchar(max)
RETURNS nvarchar(max)
    DECLARE @Pos1 int;
    DECLARE @Pos2 int;
    DECLARE @hex varchar(316);
    DECLARE @Stage table
        [Char] char(1),
        [Pos] int

    INSERT @Stage
         , [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;

      FROM @Stage
     WHERE ([Pos] IN (@Pos1, @Pos2));

    WHILE (1 = 1)
            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

              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, '');

    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), '}'))
        SELECT @rtf = SUBSTRING(@rtf, 1, (LEN(@rtf + 'x') - 2));
        IF LEN(@rtf) = 0 BREAK
    SET @Pos1 = CHARINDEX('\''', @rtf);

    WHILE @Pos1 > 0
            IF @Pos1 > 0
                    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);

    SET @rtf = @rtf + ' ';

    SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);

    WHILE @Pos1 > 0
            SET @Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1);

            IF @Pos2 < @Pos1
                SET @Pos2 = CHARINDEX('\', @rtf, @Pos1 + 1);

            IF @Pos2 < @Pos1
                    SET @rtf = SUBSTRING(@rtf, 1, @Pos1 - 1);
                    SET @Pos1 = 0;
                    SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '');
                    SET @Pos1 = PATINDEX('%\%[0123456789][\ ]%', @rtf);

    IF RIGHT(@rtf, 1) = ' '
        SET @rtf = SUBSTRING(@rtf, 1, LEN(@rtf) -1);

    RETURN @rtf;

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 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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
Germany Germany
30+ years experience as developer with VB.NET, VB, VBA, VBScript, C#, WPF, WinForms, JavaScript, jQuery, PHP, Delphi, ADO, ADO.NET, ASP.NET, Silverlight, HTML, CSS, XAML, XML, T-SQL, MySQL, MariaDb, MS-ACCESS, dBase, OLE/COM, ActiveX, SEPA/DTAUS, ZUGFeRD, DATEV Format and DATEVconnect, DSGVO, TNT Web-API, MS-Office Addins, etc., including:
- 10+ years experience as developer and freelancer
- 10+ years experience as team leader
- 13+ years experience with CRM solutions

Comments and Discussions

GeneralRe: getting error Pin
NightWizzard16-Sep-16 5:39
MemberNightWizzard16-Sep-16 5:39 
GeneralRe: getting error Pin
Member 119132835-Oct-16 4:59
MemberMember 119132835-Oct-16 4:59 
GeneralRe: getting error Pin
NightWizzard5-Oct-16 7:10
MemberNightWizzard5-Oct-16 7:10 
GeneralRe: getting error Pin
Member 119132835-Oct-16 4:59
MemberMember 119132835-Oct-16 4:59 
QuestionConverting RTF to plain text (for RTF documents that can be read by Microsoft Word) Pin
Member 1245022910-Apr-16 15:19
MemberMember 1245022910-Apr-16 15:19 
AnswerRe: Converting RTF to plain text (for RTF documents that can be read by Microsoft Word) Pin
NightWizzard10-Apr-16 22:15
MemberNightWizzard10-Apr-16 22:15 
AnswerRe: Converting RTF to plain text (for RTF documents that can be read by Microsoft Word) Pin
NightWizzard11-Apr-16 6:34
MemberNightWizzard11-Apr-16 6:34 
QuestionThere are some \f1 still left over Pin
ozz.project9-Apr-16 4:44
Memberozz.project9-Apr-16 4:44 
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
AnswerRe: There are some \f1 still left over Pin
NightWizzard9-Apr-16 6:32
MemberNightWizzard9-Apr-16 6:32 
AnswerRe: There are some \f1 still left over Pin
NightWizzard11-Apr-16 6:33
MemberNightWizzard11-Apr-16 6:33 
QuestionMany thanks! Pin
chaprot1-Apr-16 5:49
Memberchaprot1-Apr-16 5:49 
AnswerRe: Many thanks! Pin
NightWizzard1-Apr-16 6:01
MemberNightWizzard1-Apr-16 6:01 
Questionfine work! Pin
Member 123751417-Mar-16 3:44
MemberMember 123751417-Mar-16 3:44 
AnswerRe: fine work! Pin
NightWizzard7-Mar-16 6:44
MemberNightWizzard7-Mar-16 6:44 
Questionsome formatting remains Pin
Mauro S4-Feb-16 2:21
MemberMauro S4-Feb-16 2:21 
AnswerRe: some formatting remains Pin
NightWizzard4-Feb-16 2:28
MemberNightWizzard4-Feb-16 2:28 
GeneralRe: some formatting remains Pin
Mauro S4-Feb-16 3:13
MemberMauro S4-Feb-16 3:13 
GeneralRe: some formatting remains Pin
Mauro S4-Feb-16 3:44
MemberMauro S4-Feb-16 3:44 
GeneralRe: some formatting remains Pin
NightWizzard4-Feb-16 7:38
MemberNightWizzard4-Feb-16 7:38 
GeneralRe: some formatting remains Pin
Mauro S4-Feb-16 8:58
MemberMauro S4-Feb-16 8:58 
QuestionFormatting after text Pin
Member 122544129-Jan-16 12:00
MemberMember 122544129-Jan-16 12:00 
AnswerRe: Formatting after text Pin
NightWizzard10-Jan-16 2:00
MemberNightWizzard10-Jan-16 2:00 
GeneralRe: Formatting after text Pin
Member 1225441210-Jan-16 4:27
MemberMember 1225441210-Jan-16 4:27 
QuestionOdd results using the function Pin
sohst8-Dec-15 8:28
Membersohst8-Dec-15 8:28 
AnswerRe: Odd results using the function Pin
NightWizzard8-Dec-15 9:12
MemberNightWizzard8-Dec-15 9:12 

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.