In this blog, I will demonstrate how to convert string into table list the dynamically using MS-SQL user define function.

Create a function:

   When we start programming in any language, mostly we use loop & every time to hit server side code for list data. This function helpful to reduce our server side code & increase application performance.

 

CREATE FUNCTION [dbo].[UF_StrToTable] ( @String VARCHAR(MAX),@Delimiter CHAR(1))RETURNS @Temptable TABLE (Result VARCHAR(8000))

AS

BEGIN

DECLARE @INDEX int , @SLICE VARCHAR(8000)

SELECT @INDEX = 1

IF LEN(@String)<1 OR @String IS NULL return

WHILE @INDEX!= 0

BEGIN

SET @INDEX = CHARINDEX(@Delimiter,@String)

IF @INDEX!=0

BEGIN

SET @SLICE = LEFT(@String,@INDEX – 1)

END

ELSE

BEGIN

SET @SLICE = @String

END

IF(LEN(@SLICE)>0)

BEGIN

INSERT INTO @Temptable(Result) VALUES(@SLICE)

END

SET @String = RIGHT(@String,LEN(@String) – @INDEX)

IF LEN(@String) = 0 break

END

RETURN

END

Output:

We can send the list as a string, it converts against as a list of rows.

SELECT Result FROM DBO.UF_StrToTable(‘1,2,3,4,5′,’,’)

Run the above Query in sql server & see the result

sql1.png

 

Advertisements