CODEWITHSUNDEEP
sql-serversubstringudfpatindex
user3486773
user3486773

Reputation: 1246

How to find and display all instances of a pattern in a SQL Server string?

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.

Upvotes: 2

Views: 11412

Answers (4)

Rodrigo Cerqueira Lopes
Rodrigo Cerqueira Lopes

Reputation: 1

I'm currently using like this:

CREATE FUNCTION [dbo].[f_get_all_patindex](@pat NVARCHAR(256), @str NVARCHAR(MAX))
RETURNS TABLE 
AS
RETURN WITH cte AS (
    SELECT
        @str AS substr,
        PATINDEX(@pat, @str) AS substr_ix_found, 
        CONVERT(INT, 0) AS index_found
    UNION ALL
    SELECT 
        SUBSTRING(cte.substr, cte.substr_ix_found + 1, LEN(@str)), 
        PATINDEX(@pat, SUBSTRING(cte.substr, cte.substr_ix_found + 1, LEN(@str))) ,
        CONVERT(INT, cte.index_found + cte.substr_ix_found)
    FROM cte WHERE cte.substr_ix_found > 0
)
SELECT cte.index_found
FROM cte 
WHERE cte.index_found > 0 
GO

SELECT * FROM [dbo].[f_get_all_patindex]('%HH%', '123456HHJ78987HHJ654321qwertyuiopasdfghjklHHHJzxcvbnm')

--the example from the question:

DECLARE @str NVARCHAR(MAX) = N'random text here Task 1234 blah blah more text task 4567'
SELECT index_found, SUBSTRING(@str, index_found, 9) FROM [dbo].[f_get_all_patindex]('%Task%', @str)

Could be better performance-wise, and did not made extensive tests, but I prefer than the other solutions because it gives the index just using PATINDEX and SUBSTRING

Upvotes: 0

Mani
Mani

Reputation: 344

use the below code and convert in to function

declare @str varchar(100)='random text here Task 1234 blah blah more text task 4567 random task 9556'
declare @gstr varchar(100)=''--for saving the output string
declare @flag int=1--to identify the pattern starting
declare @move int=1-- change start of the string 
 while  PATINDEX('%Task%', substring(@str,@move,LEN(@str)))>0
begin
set @str=substring(@str,@move,LEN(@str))
set @flag= PATINDEX('%Task%', @str)
set @gstr= @gstr+','+SUBSTRING(@str, @flag,9)
set @move=@flag+9
end
select STUFF(@gstr,1,1,'')

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

If you need more SET-based solution, you can search as follows:

DECLARE @pattern nvarchar(MAX) = N'%task [0-9][0-9][0-9][0-9]%';
DECLARE @input nvarchar(MAX) = N'random text here Task 1212 blah blah more text task 4567';
WITH Src AS
(
    SELECT
        SUBSTRING(@input, PATINDEX(@pattern, @input), 9) Val,
        STUFF(@input, 1, PATINDEX(@pattern, @input)+9, '') Txt
        WHERE PATINDEX(@pattern, @input)>0
    UNION ALL
    SELECT
        SUBSTRING(Txt, PATINDEX(@pattern, Txt), 9),
        STUFF(Txt, 1, PATINDEX(@pattern, Txt)+9, '')
    FROM Src
    WHERE PATINDEX(@pattern, Txt)>0
)
SELECT STUFF((SELECT ', '+Val FROM Src FOR XML PATH('')), 1, 2, '')

Upvotes: 3

Matt
Matt

Reputation: 14341

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.

Upvotes: 3

Related Questions