我有一个T-SQL函数,如下所示,当目标字符串中有特定子串时,需要返回一个特定的值。
我试图研究这个问题,但是我看到不一致的地方,是
(似乎是正确的)还是
CHARINDEX(string, searchstring)
,并且研究了
CHARINDEX(searchstring, string)
和
LIKE
的替代品。
PATINDEX
所有这三种方法在明显的情况下都没有匹配结果(很可能我只是做错了什么),或者它起作用了,但也给出了假阳性结果。
在下面的例子中,案例'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;
IF( CHARINDEX(@intAttribute, '2ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 2;
IF( CHARINDEX(@intAttribute, '3ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 3;
IF( CHARINDEX(@intAttribute, '4ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 4;
IF( CHARINDEX(@intAttribute, '5ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 5;
IF( CHARINDEX(@intAttribute, '6ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 6;
IF( CHARINDEX(@intAttribute, '7ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 7;
IF( CHARINDEX(@intAttribute, '8ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 8;
IF( CHARINDEX(@intAttribute, '9ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 9;
IF( CHARINDEX(@intAttribute, '10ST') > 0 )
BEGIN
SET @intRunningTotal = @intRunningTotal + 10;
RETURN @intRunningTotal;
当前的测试语句(以防它们以某种方式出错)。
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
除了不定义参数长度的问题外,我不会实现这样的逻辑--或者说根本不会实现标量UDF。
虽然你没有显示出来,但据推测,由于你的函数是针对每一种情况进行测试的,你可以传递一个有多个限定值的字符串,你想对其进行求和。
你应该总是尝试编写 表值 函数,这些函数的速度要快几个数量级,特别是在使用较大的结果集时。
根据你所展示的内容,请尝试以下内容。
create or alter function SDACalculateAttributeStaticBonus (@intAttribute varchar(50)) returns table return ( select [Value]=Sum(try_convert(int,value)) from string_split(replace(@intAttribute, 'ST', ','), ',')
像其他表格一样使用,并从中选择,连接到它或应用它。