Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I find all the positions with patindex in a table or variable?

declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  '
  + 'dokhtare khoshkel be disco raft va ali baraye'
  + ' 1 saat anja bud va sepas... ali...'
select patindex('%ali%',@name) as pos 

This returns 1 but I want all results, e.g.:

pos
===
  1
 74
113
share|improve this question

4 Answers 4

up vote 6 down vote accepted
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...'

Declare @a table (pos int)
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex('%ali%',@name) 
while @pos > 0 and @oldpos<>@pos
 begin
   insert into @a Values (@pos)
   Select @oldpos=@pos
   select @pos=patindex('%ali%',Substring(@name,@pos + 1,len(@name))) + @pos
end

Select * from @a

To make it reuseable you can use it in a table function to call it like:

Select * from  dbo.F_CountPats ('ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...','%ali%')

The function could look like this

Create FUNCTION [dbo].[F_CountPats] 
(
@txt varchar(max),
@Pat varchar(max)
)
RETURNS 
@tab TABLE 
(
 ID int
)
AS
BEGIN
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex(@pat,@txt) 
while @pos > 0 and @oldpos<>@pos
 begin
   insert into @tab Values (@pos)
   Select @oldpos=@pos
   select @pos=patindex(@pat,Substring(@txt,@pos + 1,len(@txt))) + @pos
end

RETURN 
END

GO
share|improve this answer

I think this will be slightly more efficient than the looping method you've chosen (and definitely more efficient than the recursive CTE):

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) 
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string))
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Sample usage:

DECLARE @name NVARCHAR(MAX);

SET @name = N'ali reza dar yek shabe barani ba yek'
    + '  dokhtare khoshkel be disco raft va ali baraye '
    + '1 saat anja bud va sepas... ali...';

SELECT pos FROM dbo.FindPatternLocation(@name, 'ali');

Results:

pos
---
  1
 74
113

If your strings will be longer than 2K then use sys.all_columns instead of sys.all_objects. If longer than 8K then add a cross join.

share|improve this answer

--Recursive CTE

with cte as
(select 'ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...' as name
), 
pos as
(select patindex('%ali%',name) pos, name from cte
union all
select pos+patindex('%ali%',substring(name, pos+1, len(name))) pos, name from pos
where patindex('%ali%',substring(name, pos+1, len(name)))>0
)
select pos from pos
share|improve this answer

I love Aaron Bertrand's answer. Although I don't understand it completely, it looks really elegant.

In the past I've ran into problems with permissions when using sys.objects. Combined with the need for me to troubleshoot the code, I've come up with a variation on Aaron's code, and added it below.

This is my procedure:

CREATE PROCEDURE dbo.FindPatternLocations
-- Params
@TextToSearch nvarchar (max),
@TextToFind nvarchar (255)

AS
BEGIN

    declare @Length int
    set @Length = (Select LEN(@TextToSearch))

    declare @LengthSearchString int
    set @LengthSearchString = (select LEN (@TextToFind))

    declare @Index int
    set @Index=1

    create table #Positions (
    [POSID] [int] IDENTITY(0,1) NOT FOR REPLICATION NOT NULL,
    POS int
    )

    insert into #Positions (POS) select 0 -- to return a row even if no findings occur

        set @Index = (select charindex(@TextToFind, @TextToSearch, @Index))
                    if @Index = 0 goto Ende -- TextToFind is not in TextToSearch

        insert into #Positions (POS) select @Index


        set @Index = @Index + @LengthSearchString

while @Index <= @Length - @LengthSearchString   
    Begin
            set @Index = (select charindex(@TextToFind, @TextToSearch, @Index) )
            if @Index = 0 goto Ende -- no findings anymore
            insert into #Positions (POS) select @Index
            set @Index = @Index + @LengthSearchString
    end
Ende:
if (select MAX(posid) from #Positions) > 0 delete from #Positions where POSID = 0 -- row is not needed if TextToFind occurs
select * from #Positions
END
GO

The MAX(posid) value is also the number of matches found.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.