Technical Article

Split and concatenate function

,

Function has two input parameters; a string and delimiter.

Parameter string will be used in function for:

1) to split the values

2) add any additional logic as denoted in part with

/* --- COPY/PAST YOUR CASE STATEMENT --- */

3) concatenating new values back to string

4) returning string.

Parameter delimiter is simply a char, used in function to destinguish how to split a string.

E.g.: '1,3,5,7,2,1' uses comma for delimitation between the values.

Alter function and taylor it to your needs in section marked with START and END. It uses CASE statement, but can easly be replaced with any other statement. Also joins can be added for bounding on to your data.

Usage for function is as following:

DECLARE @myString VARCHAR(10) = '1,1,2,2,42'

DECLARE @delimiter CHAR(1) = ','

SELECT

     @myString AS inputString

    ,[dbo].[Split_and_concatenate](@myString, @delimiter) AS outputString

-- Split and concatenate back to string
CREATE FUNCTION [dbo].[Split_and_concatenate]
(
        @str NVARCHAR(MAX)
   ,@delimiter CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS 
BEGIN

       
       DECLARE @pos INT = 0
       DECLARE @i INT = 1
       DECLARE @nextPos INT
       DECLARE @item NVARCHAR(4000)
       DECLARE @maxLen INT = LEN(@str)
       DECLARE @delim CHAR(1) 
       DECLARE @tableItem TABLE (
                                  value INT
                                 ,doSomething VARCHAR(50)
                                 ,GROUPBY CHAR(1)
                                )
       DECLARE @returnValues VARCHAR(500) 

   SET @delim = @delimiter

       WHILE @maxLen >= @pos
       BEGIN  
                SET @nextPos = CHARINDEX(@delimiter, @str, @pos);

                IF (@nextPos = 0 OR @nextPos IS NULL) 
                            SELECT @nextPos = @maxLen + 1;

                SET @item = LTRIM(RTRIM(SUBSTRING(@str, @pos, @nextPos - @pos)));
                    
                    INSERT INTO @tableItem
                    SELECT @item
/*  START: --- COPY/PASTE YOUR CASE STATEMENT --- */                           ,CASE 
                                  WHEN @item = 1 THEN '11'
                                  WHEN @item = 2 THEN '22' 
ELSE '33'
      
                             END
/*  END: --- COPY/PASTE YOUR CASE STATEMENT --- */                             ,1   AS GROUPBY 
                SET @pos = @nextPos+LEN(@delimiter)      
                SET @i = @i + 1

       END

       SELECT  @returnValues =
             SUBSTRING((SELECT '; '+ST1.doSomething  AS [text()]
                                  FROM 
@tableItem AS ST1
                                  WHERE 
ST1.GROUPBY = ST2.GROUPBY
                                  FOR XML PATH ('')),2, 10000) 
                 
 FROM @tableItem AS ST2


RETURN @returnValues

END;
GO


-- HOW TO USE Function
DECLARE @myString VARCHAR(10) = '1,1,2,2,42'
DECLARE @delimiter CHAR(1) = ','

SELECT
 @myString AS inputString
,[dbo].[Split_and_concatenate](@myString, @delimiter) AS outputString

Rate

3 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating