So I am trying to write a UDF that will pull specific data from a string.
'random text here Task 1234 blah blah more text task 4567'
I want to extract 'Task 1234 and task 4567' and have them display like such
'Task 1234, task 4567'
Here's what I got so far, but only seem to be able to get either the first task or the second but not both.
Alter Function [dbo].[fn_GetTask](@strText VARCHAR(MAX))
RETURNS varchar(1000)
AS
BEGIN
while patindex('%Task%', @strText) > 0
BEGIN
SET @strText = substring(@strText, (CHARINDEX(substring(@strText, Patindex('%Task%', @strText) +4, 5), @strText, 5)),5)
end
RETURN @strText
END
It's been a very long day. I feel like I'm missing something really basic here.
ALTER Function [dbo].[fn_GetTask](@strText VARCHAR(MAX))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @ReturnString VARCHAR(1000) = ''
WHILE PATINDEX('%Task%', @strText) > 0
BEGIN
DECLARE @FoundString VARCHAR(1000) = SUBSTRING(@strText,PATINDEX('%Task%',@strText),9)
IF (LEN(@ReturnString) > 0)
BEGIN
SET @ReturnString += ', '
END
SET @ReturnString += @FoundString
SET @strText = RIGHT(@strText,LEN(@strText) - PATINDEX('%' + @FoundString + '%',@StrText))
END
RETURN @ReturnString
END
As I had mentioned during your while loop you where over writing your string you where searching through with your results string. you didn't have a mechanism to trim that original string or track your progress here is one way to do it, I used additional variables just so I didn't have to nest the same statement a couple of times.