VERY simple SQL RTF to TXT converter primarily to convert Act notes to plain text

RTF2TXTfn.sql

/*
	Written by:		patpawlowski
	Created On:		Oct 26, 2015 at 4:51:52 PM
        Description:            This is a rough attempt to create a funciton to strip the markup from 
                                    the Act TBL_NOTE.NOTETEXT field. It appears to work for what I need
                                    but could probably use some work with the escaped characters. 

                                It's not particularly fast but it is faster than other solutions I've come 
                                    across. It takes about 4 seconds to parse 2700 records. 
        File:                   RTF2TXTfn.sql
*/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RTF2TXT]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].RTF2TXT
GO

CREATE FUNCTION RTF2TXT
(@In VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
	If isnull(@In,'') = '' return ''
	If @In not like '{\rtf%' return @In
	Declare @Len int
	Declare @Loc int = 1
	Declare @Char char(1) = ''
	Declare @PrevChar char(1) = ''
	Declare @NextChar char(1) = ''
	Declare @InMarkup int = 0
	Declare @InBrackets int = -1
	Declare @Out varchar(max) = ''

		Set @Len = len(@In)
		While @Loc < @Len begin
			Set @PrevChar = @Char
			Set @Char = SUBSTRING(@In, @Loc, 1)
			If @Loc < @Len set @NextChar = SUBSTRING(@In, @Loc + 1, 1) else set @NextChar = ''
			Set @Loc = @Loc + 1
			If @Char = '{' and @PrevChar != '\' begin
				Set @InBrackets = @InBrackets + 1
				Continue
			End
			If @Char = '}' and @PrevChar != '\' begin
				Set @InBrackets = @InBrackets - 1
				Continue
			End
			If @Char = '\' and @PrevChar != '\' and @NextChar not in ('\','{','}','~','-','_') begin
				Set @InMarkup = 1
				continue
			End
			If @Char = ' ' or @Char = char(13) begin
				Set @InMarkup = 0
			
			End
			If @InMarkup > 0 or @InBrackets > 0 continue
		
			Set @Out = @Out + @Char

		End

	Set @Out = replace(@Out, '\\', '\')
	Set @Out = replace(@Out, '\{', '{')
	Set @Out = replace(@Out, '\}', '}')
	Set @Out = replace(@Out, '\~', ' ')
	Set @Out = replace(@Out, '\-', '-')
	Set @Out = replace(@Out, '\_', '-')

	WHILE ASCII(@Out) < 33
	BEGIN
	set @Out = substring(@Out,2,len(@Out))
	END

	set @Out = reverse(@Out)

	WHILE ASCII(@Out) < 33
	BEGIN
	set @Out = substring(@Out,2,len(@Out))
	END

	set @Out = reverse(@Out)

	RETURN LTRIM(RTRIM(@Out))
End

This has saved my life. Thank you!!!

Très utile, merci.

thank you soooo much😍

It worked like a charm. Thanks.

Thank you. It works perfectly

Brilliant stuff, runs very efficient!!

You sir are a legend! Thank you very much!

Thank you very much

is there any other way around text to rtf?

excellent utility. thanks!

Thanks for this work. unfortunately, it does not work fot all the records in my database as shown below

Does anyone can help me ?

This is incredible, really solid function. It is slow but honestly, speed is the least of my worries as long as the job gets done. Thanks a lot for your work here Patpawlowski.