我有一個如下所列的 T-SQL 函式，當特定子字串在目標字串中時，它需要回傳一個特定值。
我試圖對此進行研究，但我發現它是CHARINDEX(string, searchstring)（似乎是正確的）還是不一致CHARINDEX(searchstring, string)，并研究了LIKEandPATINDEX替代方案。
這三種方法在明顯的情況下都沒有匹配（很可能我只是做錯了什么），或者它有效，但也會產生誤報。
在下面的示例中，案例 '1ST' 和 '10ST' 都回傳，就好像兩者都被找到一樣，就好像它是 '1ST10ST'。
如果您能指出錯誤，我確定這是我缺少的一些簡單格式。
的答案LIKE，CHARINDEX歡迎PATINDEX。
功能：
CREATE OR ALTER FUNCTION SDACalculateAttributeStaticBonus
(@intAttribute VARCHAR)
RETURNS int
BEGIN
DECLARE @intRunningTotal int = 0;
IF( CHARINDEX(@intAttribute, '1ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 1;
END
IF( CHARINDEX(@intAttribute, '2ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 2;
END
IF( CHARINDEX(@intAttribute, '3ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 3;
END
IF( CHARINDEX(@intAttribute, '4ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 4;
END
IF( CHARINDEX(@intAttribute, '5ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 5;
END
IF( CHARINDEX(@intAttribute, '6ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 6;
END
IF( CHARINDEX(@intAttribute, '7ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 7;
END
IF( CHARINDEX(@intAttribute, '8ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 8;
END
IF( CHARINDEX(@intAttribute, '9ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 9;
END
IF( CHARINDEX(@intAttribute, '10ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 10;
END
RETURN @intRunningTotal;
END
當前的測驗陳述句（以防它們以某種方式出錯）：
SELECT DISTINCT '1ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('1ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '2ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('2ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '3ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('3ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '4ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('4ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '5ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('5ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '6ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('6ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '7ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('7ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '8ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('8ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '9ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('9ST') AS StrengthBonus
FROM HeroesViewMultiLine
SELECT DISTINCT '10ST' AS Strength, dbo.SDACalculateAttributeStaticBonus('10ST') AS StrengthBonus
FROM HeroesViewMultiLine
uj5u.com熱心網友回復：
除了沒有定義引數長度的問題之外，我根本不會實作這樣的邏輯 - 或標量 UDF。
盡管您沒有顯示它，但大概是因為您的函式正在針對每種情況進行測驗，您可以傳遞一個包含多個要求和的限定值的字串。
您應該始終嘗試撰寫速度快幾個數量級的表值函式，尤其是在與較大的結果集一起使用時。
根據您顯示的內容，請嘗試以下操作：
create or alter function SDACalculateAttributeStaticBonus (@intAttribute varchar(50))
returns table
as
return (
select [Value]=Sum(try_convert(int,value))
from string_split(replace(@intAttribute, 'ST', ','), ',')
);
像使用任何其他表一樣使用并從中選擇、加入或應用它：
select [Value] from dbo.SDACalculateAttributeStaticBonus('10ST');
結果：10
select [value] from dbo.SDACalculateAttributeStaticBonus('1ST2ST3ST');
結果：6
SELECT DISTINCT '10ST' AS Strength, b.[value] AS StrengthBonus
FROM HeroesViewMultiLine
CROSS APPLY dbo.SDACalculateAttributeStaticBonus('10ST')b;
uj5u.com熱心網友回復：
因為您的引數長度可能被宣告為高于您的輸入字串長度，否則它將為 1 作為默認值。
除此之外，在您的情況下，我會使用ELSE IF而不是多個IF，因為它可以獲得更好的性能。
create or alter FUNCTION SDACalculateAttributeStaticBonus(
@intAttribute VARCHAR(10)
)
RETURNS int
BEGIN
DECLARE @intRunningTotal int = 0;
IF( CHARINDEX(@intAttribute, '1ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 1;
END
ELSE IF( CHARINDEX(@intAttribute, '2ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 2;
END
ELSE IF( CHARINDEX(@intAttribute, '3ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 3;
END
ELSE IF( CHARINDEX(@intAttribute, '4ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 4;
END
ELSE IF( CHARINDEX(@intAttribute, '5ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 5;
END
ELSE IF( CHARINDEX(@intAttribute, '6ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 6;
END
ELSE IF( CHARINDEX(@intAttribute, '7ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 7;
END
ELSE IF( CHARINDEX(@intAttribute, '8ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 8;
END
ELSE IF( CHARINDEX(@intAttribute, '9ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 9;
END
ELSE IF( CHARINDEX(@intAttribute, '10ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal 10;
END
RETURN @intRunningTotal;
END
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/455693.html