RTF to HTML conversion through TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

RTF to HTML conversion through TSQL

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-12 : 05:47:51

Following function by Peter Larsson helped me to conver my RTF data to plain text, but what if i need to convert it to HTML.

CREATE FUNCTION dbo.fnRTFtoPT
(
@rtf VARCHAR(MAX)
)
RETURNS VARCHAR(8000)
AS
BEGIN
IF @RTF IS NOT NULL AND @RTF <> ''
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', CHAR(32)),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)

SELECT @rtf = REPLACE(@rtf, '\b0 ', ' '),
@rtf = REPLACE(@rtf, '\b ', ' ')
SELECT @rtf = replace(@rtf,'\plain\f0\fs28','')
SELECT @rtf = replace(@rtf,'\plain\f0\fs20','')
SELECT @rtf = replace(@rtf,'\plain\f0\fs','')
SELECT @rtf = replace(@rtf,'\cf0','')
SELECT @rtf = replace(@rtf,'\b\ul','')

SELECT @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')
SELECT @rtf = replace(@rtf,'\plain\f0\fs20','')

END
RETURN @rtf
END

--------------------------
http://connectsql.blogspot.com/

AASC
Starting Member

24 Posts

Posted - 2014-10-19 : 17:53:27

Code on below link may help you
http://sqlcache.blogspot.com/2014/10/rtf-to-html-conversion.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources