CREATE FUNCTION	dbo.fnParseRTF
(
	@rtf VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE	@Stage TABLE
		(
			Chr CHAR(1),
			Pos INT
		)

	INSERT		@Stage
			(
				Chr,
				Pos
			)
	SELECT		SUBSTRING(@rtf, Number, 1),
			Number
	FROM		master..spt_values
	WHERE		Type = 'p'
			AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')

	DECLARE	@Pos1 INT,
		@Pos2 INT

	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 AS s1
			INNER JOIN	@Stage AS s2 ON s2.Pos > s1.Pos
			WHERE		s1.Chr = '{'
					AND s2.Chr = '}'
			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	@Pos1 = PATINDEX('%\cf[0123456789][0123456789 ]%', @rtf)

	WHILE @Pos1 > 0
		SELECT	@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
			@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
			@Pos1 = PATINDEX('%\cf[0123456789][0123456789 ]%', @rtf)

	SELECT	@rtf = REPLACE(@rtf, '\pard', ''),
		@rtf = REPLACE(@rtf, '\par', ''),
		@rtf = LEFT(@rtf, LEN(@rtf) - 1)

	SELECT	@rtf = REPLACE(@rtf, '\b0 ', ''),
		@rtf = REPLACE(@rtf, '\b ', '')

	SELECT	@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')

	RETURN	@rtf
END

DECLARE	@Sample TABLE (ID INT, data VARCHAR(8000))

INSERT	@Sample
SELECT	1, '{\rtf1\ansi\ansicpg1252\deff0\deflang1053{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
{\colortbl ;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;}
{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\f0\fs20 P\cf1 e\cf0 ter\par
\cf2 L\cf0 ar\b s\b0 s\cf3 o\cf0 n\par
}' UNION ALL
SELECT	2, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}
{\colortbl ;\red0\green0\blue0;}
{\*\generator Riched20 5.50.99.2014;}\viewkind4\uc1\pard\cf1\f0\fs18\lang1033 ~200 LF streambank stabilization to provide structural protection\par}'

SELECT	ID,
	dbo.fnParseRTF(data)
FROM	@Sample

UNION ALL
SELECT	3, '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}{\f1\froman\fcharset0 Times New Roman;}}
\viewkind4\uc1\pard\f0\fs20 This is an \lang2057\f1\fs24\ldblquote quoted text\rdblquote  of pretty-quotes\lang1033\f0\fs20\par
}'

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