# Trying to check if a string contains a number exclusively

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

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

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
``````
• 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