3

I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string is '7+16+2' it should return false, because the '6' in this string is part of the number '16')

I wrote the function below (It is lengthy but I was going to test it first before refactoring it)

Upon testing I found a bug whereby it only runs the first instance of a found number through the logic. So running this function with '6' against '16+7+9+6' would return false because it determines that the firrst '6' is part of a bigger number and stops processing.

I figured that to get around this I have to implement a loop to shorten the 'haystack' string (so that, using the example '16+7+9+6' the function continues checking '+7+9+6' after eliminating the first '6') but before spending the time to make an already convoluted function even more convoluted I wanted to check if there is a simpler way to acheive the same goal?

drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as 
begin

    /*
    eliminate the plus sign from @runners so that the 
    'isnumeric' function doesn't return false positives (it returns 1 for '+') 
    */
    set @runners = replace(@runners,'+','_' ) 


    declare @ret int;
    set @ret = 0;

    -- if the runner is the only runner return 1
    if @runners = @runner 
        set @ret = 1
    else
    begin
        declare @charindex int;
        set @charindex = charindex(@runner,@runners)
        if @charindex > 0
        begin

            -- if it is at the beginning then check the char after it 
            if @charindex = 1 
            begin
                if isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end

            -- if it is at the end then check the char before it
            else if @charindex = len(@runners) - (len(@runner) - 1) 
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) = 0
                    set @ret = @charindex
            end

            -- if it is in the middle check the chars either side of it
            else
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) + 
                isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex 
            end
        end
    end
    return @ret
end
  • If the incoming string is in the form of numbers separated by plus (+) signs, can't you just implement a simple split Table Valued Function to split on ``+` and then select from the TVF table where the split value is equal to the incoming number? – Scott Hodgin Nov 2 '18 at 13:56
  • @ScottHodgin the incoming string will usually be in that form, but sometimes it might contain commas or dashes, so I wanted to write this function to cover that. I did look into a split function but before I go down that road I wanted to find out if there is a simpler way, and avoid re-inventing the wheel. – MrVimes Nov 2 '18 at 13:59
  • Have you considered using regular expression match instead? – mustaccio Nov 2 '18 at 20:44
  • @mustaccio Hey. RegEx isn't needed here since this isn't that complicated of a pattern. Please see my answer for details. – Solomon Rutzky Nov 2 '18 at 21:36
5

Perhaps you are over-complicating this by focusing too much on wanting a number. Take a step back for a moment. What you actually want is a substring without any digits on either side of it. The only way a number could be part of a larger number is to have at least 1 digit on either side of it, right? So as long as you only pass in numbers, then this definition should still produce numbers that do not have any digits on either side.

With that in mind, we just need 3 PATINDEX predicates to cover the passed-in value being on the far left, on the far right, or in the middle. Try the following as it seems to work:

GO
CREATE PROCEDURE #TestFindRunner
(
  @Runner VARCHAR(10)
)
AS
SET NOCOUNT ON;

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.*
FROM   @Data tmp
WHERE  @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners]
OR     PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0
GO

And then test with:

EXEC #TestFindRunner 0;
EXEC #TestFindRunner 2;
EXEC #TestFindRunner 4;
EXEC #TestFindRunner 8;
EXEC #TestFindRunner 11;
-- 0 rows

EXEC #TestFindRunner 3;   -- 4
EXEC #TestFindRunner 77;  -- 3
EXEC #TestFindRunner 111; -- 5
-- 1 row

EXEC #TestFindRunner 5; -- 2 and 3
-- 2 rows

EXEC #TestFindRunner 1; -- 6, 7, 8, and 9
-- 4 rows

EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11
-- 5 rows

EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9
-- 6 rows

EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9
-- 7 rows

The reason for having 3 variations of the PATINDEX is that PATINDEX search patterns are not Regular Expressions (RegeEx), contrary to what many people say / think (same with LIKE patterns). PATINDEX and LIKE patterns do not have quantifiers, so it is not possible to specify that the [^0123456789] single character replacement should be "0 or more"; it is "one and only one; no more, no less".

Forcing the binary collation (i.e. the COLLATE Latin1_General_100_BIN2 after each @Runner reference) ensures that we are only dealing with these 10 decimal digits and not any other character that might be considered equivalent


To put the above logic into an Inline Table-Valued Function (TVF) so that it is easier to use (and more efficient than a similarly easy-to-use scalar UDF), try the following:

USE [tempdb];
GO
CREATE FUNCTION dbo.IsRunnerPresent
(
  @Runner VARCHAR(10),
  @Runners VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

  SELECT CONVERT(BIT,
    CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners
           OR PATINDEX('%[^0123456789]' + @Runner
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX(@Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           THEN 1
         ELSE 0
      END) AS [RunnerFound];
GO

And then test with:

DECLARE @Runner VARCHAR(10);
SET @Runner = '6';


DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.[ID],
       tmp.[Runners],
       fnd.[RunnerFound]
FROM   @Data tmp
CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd;

Which returns:

ID    Runners          RunnerFound
1     16+7+9+6         1
2     16+7+9+5         0
3     26+77+9+5        0
4     6+3+45           1
5     63,808,111,92    0
6     1-7-9,6          1
7     1-6-9,7          1
8     1-7-9,63         0
9     1-63-9,7         0
10    NULL             0
11    6                1
  • Thanks @SolomonRutzky It works for any runner list with more than one runner but I tested with IsRunnerPresent('1','1') and it returned 0 which isn't what I want. I guess I can add some simple logic to the function you wrote to test pure equality before the pathindex stuff. – MrVimes Nov 3 '18 at 13:51
  • Too late to edit my comment. I have added "or @runner = @runners" into the if statement to handle needle and haystack being equal. – MrVimes Nov 3 '18 at 14:04
  • @MrVimes Ah, right, the single entry scenario. I can update my code to accommodate that. Also, I was going to update anyway to change this from a UDF into an inline TVF that will be even faster. Gimme a few minutes for that. I will include the @Runner = @Runners scenario this time ;-) – Solomon Rutzky Nov 3 '18 at 14:09
  • @MrVimes Done. I have converted the scalar UDF into an Inline TVF (much faster!). I have included the logic (and a new test case) for @Runner = @Runners. I have also adjust the original example code (the temp stored procedure) for the @Runner = @Runners logic and test case. – Solomon Rutzky Nov 3 '18 at 14:55
0

I have since re-written my function as follows.

Edit: I completed this before seeing Solomon Rutzky's reply to my question. His reply works and is better than mine. But I will leave this answer as an alternative.

drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as 
begin
    declare @ret int

    if @runner = @runners
        set @ret = 1
    else if charindex(@runner,@runners) = 0
        set @ret = 0
    else
    begin
        declare @foundindex int
        declare @item varchar(max)
        declare @tmp varchar(max)

        declare @position int
        set @tmp = @runners
        set @position = 1
        set @foundindex = 0

        while @position > 0
        begin
            set @foundindex = @foundindex + @position 
            set @position = charindex('+',@tmp)

            if @position > 0
            begin
                set @item = substring(@tmp,1,@position - 1)
                set @tmp = substring(@tmp,@position + 1,len(@tmp) - @position)
            end
            else
                set @item = @tmp
            if @item = @runner
            begin
                set @ret = @foundindex
                break
            end
            else
                set @ret = 0
        end
    end
    return @ret
end
  • 1
    Not sure why you would want to stick with a slow while loop instead of a much faster set-based approach. But, if it was due to not knowing how to make use of what I was suggesting, then I just updated to provide a ready-to-use UDF example of the same logic. – Solomon Rutzky Nov 2 '18 at 21:38
  • @SolomonRutzky Sorry, I just haven't had a chance to look at your answer in detail yet. I will do hopefully. I just wanted to post my rewritten function (which I did before seeing your answer) – MrVimes Nov 3 '18 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.