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