我有一个SQL字符串参数@branchNumber = '12,23,45'

我正在寻找一个查询,该查询将把我的@branchNumber参数作为输入,并返回如下所示的格式化结果:

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


所以基本上每个分支都添加[Branch].[Branch Number].&[<branchNumber>],

我尝试过使用子字符串,如下所示:

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


但是对于SELECT dbo.AR_BI_SplitBranchesString('12,54', ',')返回的结果是

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

只返回第一个分支。我有它是因为SET @Input再次被修改。但任何帮助将不胜感激。
我正在使用SQL Server 2014。

谢谢

编辑:

我有一个执行类似操作的CTE,但是正在从另一个表获取分支号。在字符串的情况下,我找不到实现它的方法。

CTE如下:

    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

最佳答案

理想情况下,这种事情应该在表示层中完成,或者通过将表参数传递给过程来完成,但是您已经表明这必须在数据库引擎中完成。

如果可以使用CLR,那可能是一个更好的选择。您可以使用String.Split在单线中执行此操作,该方法接受单个输入并返回单个输出。它可能比一堆CHARINDEX / SUBSTRING调用的性能更好,甚至可以将其标记为确定性的。

如果确实必须在TSQL中执行此操作,则可以使用start_locationCHARINDEX参数,而不是反复获取新的子字符串并更新@Input。类似于以下内容:

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]

10-07 19:38
查看更多