--
PRINT 'Create function kk_FN_UTIL_RemoveHTML'
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_FN_UTIL_RemoveHTML]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_FN_UTIL_RemoveHTML
GO

CREATE FUNCTION dbo.kk_FN_UTIL_RemoveHTML
(
	@strHTML		varchar(MAX),	-- HTML Data
	@intOptions		smallint	-- [Unused, reserved for future expansion, pass as NULL]
)
RETURNS varchar(MAX)
/* WITH ENCRYPTION */
AS
/*
 * kk_FN_UTIL_RemoveHTML	Remove HTML Tags
 * 	SELECT	dbo.kk_FN_UTIL_RemoveHTML(@MyTextString, NULL)
 *
 * See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148522
 *
 * Returns:
 *
 * varchar(MAX)
 */
BEGIN
DECLARE	@intStart	int,
	@intLength	int

	-- Replace common HTML entities with plain-text equivalent PRE
	SELECT	@strHTML = REPLACE(REPLACE(
				@strHTML
					, '<br>', CHAR(13) + CHAR(10))
					, '<br />', CHAR(13) + CHAR(10))	-- This is NOT infalible!

	SELECT	@intLength = 1	-- Force first iteration
	
	WHILE @intLength >= 1
	BEGIN
		-- Remove <...>
		SELECT	@intStart = CHARINDEX('<', @strHTML),
			@intLength = CHARINDEX('>', @strHTML) - NullIf(@intStart, 0) + 1
		IF @intLength >= 1
		BEGIN
			SELECT	@strHTML = STUFF(@strHTML, @intStart, @intLength, '')
		END
	END

	-- Replace common HTML entities with plain-text equivalent POST
	SELECT	@strHTML = REPLACE(REPLACE(REPLACE(REPLACE(
				@strHTML
					, '&lt;', '<')
					, '&gt;', '>')
					, '&nbsp;', ' ')
					, '&amp;', '&')	-- Last - to preserve nested ampersands
	
	RETURN @strHTML

/** TEST RIG

SELECT '<xxx>Leading tag', dbo.kk_FN_UTIL_RemoveHTML('<xxx>Leading tag', NULL) UNION ALL
SELECT 'Trailing tag<xxx>', dbo.kk_FN_UTIL_RemoveHTML('Trailing tag<xxx>', NULL) UNION ALL
SELECT 'Embedded<xxx>tag', dbo.kk_FN_UTIL_RemoveHTML('Embedded<xxx>tag', NULL) UNION ALL
SELECT '&lt;', dbo.kk_FN_UTIL_RemoveHTML('&lt;', NULL) UNION ALL
SELECT '&gt;', dbo.kk_FN_UTIL_RemoveHTML('&gt;', NULL) UNION ALL
SELECT '&amp;', dbo.kk_FN_UTIL_RemoveHTML('&amp;', NULL) UNION ALL
SELECT '&amp;amp; nested', dbo.kk_FN_UTIL_RemoveHTML('&amp;amp; nested', NULL) UNION ALL
SELECT '&amp;lt; nested', dbo.kk_FN_UTIL_RemoveHTML('&amp;lt; nested', NULL) UNION ALL
SELECT '<BR>', ']' + dbo.kk_FN_UTIL_RemoveHTML('<BR>', NULL) + '[' UNION ALL
SELECT '<BR />', ']' + dbo.kk_FN_UTIL_RemoveHTML('<BR />', NULL) + '[' UNION ALL
SELECT 'LT < Only', dbo.kk_FN_UTIL_RemoveHTML('LT < Only', NULL) UNION ALL
SELECT 'GT > Only', dbo.kk_FN_UTIL_RemoveHTML('GT > Only', NULL) UNION ALL
SELECT 'Error1 A < B <TAG>', dbo.kk_FN_UTIL_RemoveHTML('Error1 A < B <TAG>', NULL) UNION ALL
SELECT 'OK2 <TAG> A > B', dbo.kk_FN_UTIL_RemoveHTML('OK2 <TAG> A > B', NULL) UNION ALL
SELECT 'NULL', dbo.kk_FN_UTIL_RemoveHTML(NULL, NULL)

**/
--==================== kk_FN_UTIL_RemoveHTML ====================--
END
GO
PRINT 'Create function kk_FN_UTIL_RemoveHTML DONE'
GO
--