/* create function */
CREATE FUNCTION [dbo].[IntIDToTable]
(@vc_Ids varchar(1000),
@vc_sperator char(1)
)
-- Author Krishan Gurusinghe
-- Remark : This function takes a list if indexes in the form of a string variable and then returns
-- a table data type variable used for a select * from D in (select ID from intIDToTable(@d)
--
RETURNS @IDTable TABLE
(Id numeric(18,0))
BEGIN
DECLARE @in_Index1 AS INT, --Used to store EmployeeID delimiter(',') position in string
@vc_AnyId AS VARCHAR(15)
/* initialize working variables */
SET @in_Index1 = CHARINDEX(@vc_sperator,@vc_Ids)
/* loop through facility ids in delimited string */
WHILE (@in_Index1 > 0 OR LEN(@vc_Ids) > 0)
BEGIN
/* parse out single facility id for processing */
IF @in_Index1 > 0
BEGIN
SET @vc_AnyId = Left(@vc_Ids,@in_Index1 - 1)
SET @vc_Ids = Right(@vc_Ids,Len(@vc_Ids) - @in_Index1)
END
ELSE
BEGIN
SET @vc_AnyId = @vc_Ids
SET @vc_Ids = ''
END
INSERT @IDTable (Id)
VALUES(CAST(@vc_AnyId AS numeric(18,0)))
/* prepare to loop */
SET @in_Index1 = CHARINDEX(@vc_sperator,@vc_Ids)
END
/* return the facility ids */
RETURN
END
GO
Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT DECLARE @End INT DECLARE @Length INT SET @Start = CHARINDEX( '<' , @HTMLText ) SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText )) SET @Length = ( @End - @Start ) + 1 WHILE @Start > 0 AND @...
Comments
Post a Comment