我有一个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_location
的CHARINDEX
参数,而不是反复获取新的子字符串并更新@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]