Using a similar function to the one I posted last time you could get something that might help you on your way (I'm not sure what CK had in mind for an alternative approach with String Parsing so I'm bumbling on as before !):
- CREATE FUNCTION [dbo].[R_Test_func2](@sub nvarchar(1000))
-
RETURNS int
-
AS
-
BEGIN
-
-
DECLARE @Output int
-
DECLARE @myOutput int
-
SET @Output = 0
-
SET @myOutput = 0
-
-
WHILE patindex('%[0123456789]%', @sub) <> 0
-
BEGIN
-
SET @Output =
-
len(substring
-
(
-
substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))
-
,1
-
,CASE
-
WHEN patindex('%[^0123456789 -]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))) = 0
-
THEN len(@sub)
-
ELSE patindex('%[^0123456789 -]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)))-1
-
END
-
))
-
-
SET @sub =
-
CASE
-
WHEN patindex('%[^0123456789 -]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))) = 0
-
THEN 'a'
-
ELSE substring
-
(
-
substring(@sub,patindex('%[0123456789]%',@sub),len(@sub))
-
,patindex('%[^0123456789 -]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)))
-
,len(@sub)
-
)
-
END
-
-
SET @myOutput =
-
CASE
-
WHEN @Output > @myOutput THEN @Output ELSE @myOutput
-
END
-
END
-
RETURN @myOutput
-
-
END
The general principle is the same as before
It looks for the 1st numeric character:
- patindex('%[0123456789]%',@sub)
Then for the 1st non numeric character thereafter (this time also allowing for a space or -):
- patindex('%[^0123456789 -]%',substring(@sub,patindex('%[0123456789]%',@sub),len(@sub)))
The length of this string is stored and then the rest of the string is searched.
The resulting output will be the length of the longest string of consecutive numbers (including spaces and -)
So, you could use a WHERE statement such as:
- WHERE dbo.R_Test_Func2(notes) > 12
You could get rid of one of the two variables @Output and @myOutput by doing the SET as a CASE statement in one go but I felt this may complicate the (already less than easy to read) code too much!
As a note, another fix from the original function I posted is that it will now work if the last character is non-numeric, hence the addition of a couple of CASE statements buried in there. In the previous version if the last character was not a number then it would try and return a string of either length 0 or starting from the 0th character which I discovered it did not like very much!
Hope this helps