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 + 1,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 + 1,1)) = 0
set @ret = @charindex
end
end
end
return @ret
end
