Add additional content to existing SQL string on occurrence of comma

Question

I have a sql string parameter @branchNumber = '12,23,45'

I am looking for a query that will take my @branchNumber parameter as input and throw back a formatted result that looks as below:

[Branch].[Branch Number].&[12], [Branch].[Branch Number].&[23], [Branch].[Branch Number].&[45]

So basically for each branch add [Branch].[Branch Number].&[<branchNumber>],

I have tried using substring as below:

ALTER FUNCTION AR_BI_SplitBranchesString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT, @result NVARCHAR(MAX)

      SET @StartIndex = 1
      --IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      --BEGIN
      --      SET @Input = @Input + @Character
      --END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            SET @result = '[Branch].[Branch Number].&[' + (SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)) + '],'

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

      RETURN @result
END
GO

But for SELECT dbo.AR_BI_SplitBranchesString('12,54', ',') the result returned is

[Branch].[Branch Number].&[12],

Only returns the first branch. I have it is because SET @Input being modified again. But any assistance would be appreciated. I am using SQL Server 2014.

Thanks

EDIT:

I had a CTE that did something similar, but was getting branch number from another table. I cant find a way to implement it in case of a string.

the cte is as below:

    ALTER FUNCTION [dbo].[AR_Fn_BI_GetOperatorBranchList]
(
    -- Add the parameters for the function here
    @operatorId varchar(20),
    @applicationName varchar(100)
)
RETURNS varchar(max)
AS
BEGIN
    declare @branchList as varchar(max)

    ;WITH cte (FId, branchNumbers) AS
        (
            SELECT 1, CAST('' AS NVARCHAR(MAX)) 
            UNION ALL
            SELECT B.FId + 1, B.branchNumbers + '[Branch].[Branch Number].&[' + cast(A.branchNumber as varchar) + '],' 
            FROM 
            (
                SELECT Row_Number() OVER (ORDER BY Id) AS RN, cast(branchNumber as varchar(8)) as branchNumber
                    FROM <AnotherTable>
                    where OperatorId = @operatorId
                    and [Application] = @applicationName) A 
                INNER JOIN cte B ON A.RN = B.FId 
        )

        SELECT top 1 @branchList = '{' + subString(branchNumbers, 1, len(branchNumbers) - 1)+ '}'
        FROM cte 
        ORDER BY FId DESC
        option (maxrecursion 0)
    -- Return the result of the function

    RETURN @branchList
END

Can you send a table parameter instead of a comma-delimited string? Comma-delimited strings are the devil when it comes to database engines... — lc.
– lc., Commented Feb 5, 2016 at 2:50
unfortunately not. This is part of very big SP that takes in parameters and branchNumber is one of them. — aMazing
– aMazing, Commented Feb 5, 2016 at 3:02

lc. · Accepted Answer · 2016-02-05 04:55:32Z

2

Ideally this kind of thing should be done in a presentation layer, or by passing a table parameter to the procedure, but you've already indicated this has to be done in the database engine.

If you can use the CLR, that might be a better option. You can do this in a one-liner with String.Split which takes a single input and returns a single output. It probably would perform better than a bunch of CHARINDEX/SUBSTRING calls and you can even mark it as deterministic.

If you do have to do it in TSQL, I would use the start_location parameter of CHARINDEX rather than repeatedly taking new substrings and updating @Input. Something like the following:

ALTER FUNCTION AR_BI_SplitBranchesString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF @Input IS NULL RETURN NULL

    DECLARE @StartIndex INT = 1, @result NVARCHAR(MAX) = ''
    DECLARE @EndIndex INT = CHARINDEX(@Character, @Input, @StartIndex)

    WHILE @EndIndex > 0
    BEGIN
        IF @result <> ''
            SET @result += ', '

        SET @result += '[Branch].[Branch Number].&[' + 
            SUBSTRING(@Input, @StartIndex, @EndIndex - @StartIndex) + ']'

        SET @StartIndex = @EndIndex + 1
        SET @EndIndex = CHARINDEX(@Character, @Input, @StartIndex)             
    END

    --CHARINDEX returned -1 so we're done but we need to append the rest
    IF @result <> ''
        SET @result += ', '

    SET @result += '[Branch].[Branch Number].&[' + 
        SUBSTRING(@Input, @StartIndex, LEN(@Input)) + ']'            

    RETURN @result
END
GO

[SQL Fiddle Demo]

edited Feb 5, 2016 at 4:55

answered Feb 5, 2016 at 3:28

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aMazing Over a year ago

Thanks, I will give it a try as soon as I get to work tmw.

aMazing Over a year ago

That worked man.. much appreciated. A few more stars added to your galaxy.

lc. Over a year ago

@aMazing Sure thing, let me know if anything needs explanation.

Collectives™ on Stack Overflow

Add additional content to existing SQL string on occurrence of comma

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related