CHARINDEX (, PATINDEX, and LIKE) returning false positives in T-SQL function-CodePudding

I have a T-SQL function as listed below that needs to return a specific value when specific substrings are in the target string.

I've tried to research this, but I'm seeing inconsistencies in whether it is CHARINDEX(string, searchstring) (seems to be correct) or CHARINDEX(searchstring, string), and have looked into the LIKE and PATINDEX alternatives.

All three methods have resulted in no matches in obvious cases (most likely I just did something wrong), or it works, but also gives false positives.

In the example below, case '1ST' and '10ST' both return as if both were found as if it was '1ST10ST'.

I'm sure it is some simple format I'm missing, if you could point out the error.

Answers for LIKE, CHARINDEX and PATINDEX are welcome.

Function:

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

Current test statements (in case they're wrong somehow):

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

CodePudding user response：

Because your parameter length might be declared higher than your input string length otherwise it will be 1 as the default value.

Besides that, I would use ELSE IF instead of multiple IF in your case, because it can get better performance.

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

CodePudding user response：

Aside from the issue with not defining a parameter length, I would not be implementing logic like this - or a scalar UDF at all.

Although you don't show it, presumably since your function is testing for each case, you can pass a string with multiple qualifying values which you want to sum.

You should always try to write table valued functions which are orders of magnitude faster, especially when used with larger resultsets.

Based on what you have shown, try the following:

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', ','), ',')
);

Use as you would any other table and select from it, join to it or apply it:

select [Value] from dbo.SDACalculateAttributeStaticBonus('10ST');

Result: 10

select [value] from dbo.SDACalculateAttributeStaticBonus('1ST2ST3ST');

Result: 6

SELECT DISTINCT '10ST' AS Strength, b.[value] AS StrengthBonus
FROM HeroesViewMultiLine
CROSS APPLY dbo.SDACalculateAttributeStaticBonus('10ST')b;