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

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

 

 

1. Input parameter

 

1) @iHTMLText 

- 대상 문자열

 

2) @iIncludeNewLine =>

- '0' or '1'

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

 

 

2. function

/* SELECT DrPlazaDBNew.dbo.[fn_StripHTML]('<p>테스<br>트 &</p>','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,' ',' ') -- <br> ==> 엔터키로 변경, default : 변경안함 IF @iIncludeNewLine = '1' BEGIN SELECT @iHTMLText = REPLACE(@iHTMLText,'<br>',CHAR(13) + CHAR(10)) SELECT @iHTMLText = REPLACE(@iHTMLText,'<br/>',CHAR(13) + CHAR(10)) SELECT @iHTMLText = REPLACE(@iHTMLText,'<br />',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