How to Concatenate a Table Data by Using | And ^ Separator in SQL Server

Introduction

In this article, we learn how to concatenate a data table by using the |  and ^ separators in SQL server stored procedure & assign it to a single parameter, easily passing from the backend to the frontend whenever needed.

This happens when a primary table & child table are there, and we collect the information of the primary table along with the child table, which has multiple data of the reference id of the primary table.

Here we use the below code for this with a user-defined function.

declare @vch_DeductChkList VARCHAR(512)=NULL   -- 1|1000|9|9|0|1180.00^1|1000|9|9|0|1180.00^
-------------------------------------------------------
BEGIN
-- //* Insert In Refund Deduction Details Table  *//
-- 1|1000|9|9|0|1180.00^
DECLARE @req_values VARCHAR(1024)='',@int_ductTypId INT=NULL,@dec_ductAmt Dec(18,2)=NULL,@dec_ductgst dec(18,2)=NULL,@dec_ductcgst dec(18,2)=NULL,@ductigst dec(18,2)=NULL,@totalDuctAmt dec(18,2)=NULL,@taxCode varchar(10)=NULL;
DECLARE @TempRefundDeductionTypes as TABLE (id smallint identity(1,1),req_Values VARCHAR(MAX))
INSERT INTO @TempRefundDeductionTypes 
SELECT * FROM [UDF_Split](@vch_DeductChkList,'^');
DECLARE @loop SMALLINT=1,@count SMALLINT=0;
select @count=count(id) from @TempRefundDeductionTypes where req_Values<>''
WHILE @loop<=@count
BEGIN
select @req_values=req_values from @TempRefundDeductionTypes where id=@loop;
create table #TempRefundRequestItem (id smallint identity(1,1),req_value varchar(MAX))
INSERT INTO #TempRefundRequestItem (req_value)
SELECT Val FROM [dbo].[UDF_Split](@req_values,'|');
SELECT @int_ductTypId=req_value FROM #TempRefundRequestItem where id=1
SELECT @dec_ductAmt=req_value FROM #TempRefundRequestItem where id=2
SELECT @dec_ductgst =req_value FROM #TempRefundRequestItem where id=3
SELECT @dec_ductcgst=req_value FROM #TempRefundRequestItem where id=4
SELECT @ductigst=req_value FROM #TempRefundRequestItem where id=5
SELECT @totalDuctAmt =req_value FROM #TempRefundRequestItem where id=6
SELECT @taxCode =req_value FROM #TempRefundRequestItem where id=7

INSERT INTO [dbo].[T_RFD_DEDUCTION_DETAILS]
           ([INT_DEDUCTION_TYPE_ID]
           ,[INT_CUSTOMER_ID]
           ,[INT_REFUND_ID]
           ,[VCH_SSC_NUMBER]
           ,[DEC_AMOUNT]
           ,[DEC_CGST]
           ,[DEC_SGST]
           ,[DEC_IGST]
           ,[DEC_DEDUCTION_AMOUNT]
           ,[INT_CREATED_BY]
           ,[DT_CREATED_ON]
           ,[INT_UPDATED_BY]
           ,[DT_UPDATED_ON]
           ,[BIT_DELETED_FLAG]
		   ,[vch_TaxCode])
     VALUES
           (@int_ductTypId
           ,(SELECT int_CustomerId FROM T_RFD_REFUND_REQUEST WHERE int_RefId=@int_RefId and bit_DeletedFlag=0 )
           ,@int_RefId
           ,(select VCH_SSC_NUMBER from M_RFD_DEDUCTION_TYPE WHERE INT_DEDUCTION_TYPE_ID=@int_ductTypId and BIT_DELETED_FLAG=0)
           ,@dec_ductAmt
           ,@dec_ductcgst
           ,@dec_ductgst
           ,@ductigst
           ,@totalDuctAmt
           ,@int_CreatedBy
           ,GETDATE()
           ,NULL
           ,NULL
           ,0
		   ,@taxCode)
drop table #TempRefundRequestItem;
set @loop=@loop+1;
END;
END;

User Define Function

ALTER FUNCTION [dbo].[UDF_Split]
                        (
                        @String NVARCHAR (4000),
                        @Delimiter NVARCHAR (10)
                        )
RETURNS @ValueTable TABLE ([Val] NVARCHAR(4000))

BEGIN
	 DECLARE @NextString NVARCHAR(4000)
	 DECLARE @Pos INT
	
	 SET @NextString = ''
	 
	 --Check for trailing Comma, if not exists, INSERT
	 SET @String = @String + @Delimiter
	 
	 --Get position of first Comma
	 SET @Pos = charindex(@Delimiter,@String)
	 
	 --Loop while there is still a comma in the String of levels
	 WHILE (@pos <>  0)  
	 BEGIN
		  SET @NextString = substring(@String,1,@Pos - 1)
		  
		 
		  INSERT INTO @ValueTable ( [Val]) VALUES (@NextString)
		 
		  SET @String = substring(@String,@pos +1,len(@String))
		  
		  SET @pos  = charindex(@Delimiter,@String)
	 END
	 
	 RETURN
END