[MSSQL][Function] strip html tag all, remove html tag, 태그 없애기

프로그래밍/MS SQL

[MSSQL][Function] strip html tag all, remove html tag, 태그 없애기

도깨비방망이 2014. 6. 2. 13:51

[MSSQL][Function] strip html tag all, remove html tag, 태그 없애기

1. Input parameter

1) @iHTMLText

- 대상 문자열

2) @iIncludeNewLine =>

- '0' or '1'

- or or 을 char(13)+char(10) 으로 처리하느냐('1'), 안하느냐('0')

2. function

/*
SELECT	DrPlazaDBNew.dbo.[fn_StripHTML]('테스 트 &','0') ==> "테스트 &"
*/
CREATE FUNCTION		[dbo].[fn_StripHTML] 
		(
			@iHTMLText			VARCHAR(MAX),
			@iIncludeNewLine	VARCHAR(1)		= '0'
		)
RETURNS		VARCHAR(MAX) 
AS
BEGIN

DECLARE @Start INT
 DECLARE @End INT
 DECLARE @Length INT
 
 -- NON 꺽쇠괄호 처리
 SELECT	@iHTMLText	= REPLACE(@iHTMLText,'<','<')
 SELECT	@iHTMLText	= REPLACE(@iHTMLText,'>','>')
 SELECT	@iHTMLText	= REPLACE(@iHTMLText,'&','&')
	SELECT	@iHTMLText	= REPLACE(@iHTMLText,' ',' ')	
	
	-- ==> 엔터키로 변경, default : 변경안함
	IF	@iIncludeNewLine	= '1'
	BEGIN
		SELECT	@iHTMLText	= REPLACE(@iHTMLText,' ',CHAR(13) + CHAR(10))
		SELECT	@iHTMLText	= REPLACE(@iHTMLText,' ',CHAR(13) + CHAR(10))
		SELECT	@iHTMLText	= REPLACE(@iHTMLText,' ',CHAR(13) + CHAR(10))
	END
	 
	 
 SET @Start = CHARINDEX('<',@iHTMLText)
 SET @End = CHARINDEX('>',@iHTMLText,CHARINDEX('<',@iHTMLText))
 SET @Length = (@End - @Start) + 1
 
 WHILE @Start > 0 AND @End > 0 AND @Length > 0
 BEGIN
 SET @iHTMLText = STUFF(@iHTMLText,@Start,@Length,'')
 SET @Start = CHARINDEX('<',@iHTMLText)
 SET @End = CHARINDEX('>',@iHTMLText,CHARINDEX('<',@iHTMLText))
 SET @Length = (@End - @Start) + 1
 END
 
 
 RETURN (LTRIM(RTRIM(@iHTMLText)))
 
END

저작자표시 비영리 변경금지