Convert SQL TRF to Text

From MPSWiki
Revision as of 08:06, 15 October 2020 by Svan (talk | contribs) (Created page with "<pre> 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 CR...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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