Return to Revisions

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
--ALTER
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);

SELECT tmp.*
FROM   @Data tmp
WHERE  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
EXEC #TestFindRunner 6; -- 1, 4, 6, and 7
-- 4 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".

To put the above logic into a Scalar UDF so that it is easier to use, try the following:

USE [tempdb];
GO
CREATE FUNCTION dbo.IsRunnerPresent
(
  @Runner VARCHAR(10),
  @Runners VARCHAR(8000)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Found BIT;
  SET @Found = 0;


  IF (   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
     )
  BEGIN
    SET @Found = 1;
  END;

  RETURN @Found;
END;
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);

SELECT tmp.[ID],
       tmp.[Runners],
       dbo.IsRunnerPresent(@Runner, tmp.[Runners]) AS [RunnerFound]
FROM   @Data tmp;