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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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 know this is old question, but I have question about performance. I've build two functions that search of 1 in string that contains only zeros and ones. I've used Your solution and @aaron-bertrand, but I got same results and same performance. Which solution would it better? – Misiu Nov 2 '15 at 12:16
    
@Misiu as expected Aaron Bertrands solutions is not just more elegant but even much faster then mine and should be the accepted solution. You may test this easily with a bigger input, using his example just add SET @name=Replicate(@name,5000) before the call SELECT pos FROM dbo.FindPatternLocation(@name, 'ali'); and try the same with my slow procedure. – bummi Nov 2 '15 at 16:44

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
    
I know this is an old one, but why this is more efficient that while? I have small string that looks like this: 00001000001001 (only zeros and ones) and I must search for positions of ones and return it as comma separated string. I've used Your method and @bummi, but I can't see any differences in case of performance. – Misiu Nov 2 '15 at 12:14
    
@Misiu You're parsing a single string? Then you're unlikely to see any difference. Performance differences due to looping or recursion will become more obvious at scale. – Aaron Bertrand Nov 2 '15 at 14:59
    
I have table with 10 million+ records and doing this for every record – Misiu Nov 2 '15 at 15:05
    
@Misiu OK, if they're both the same speed in your particular scenario, then flip a coin? – Aaron Bertrand Nov 2 '15 at 16:34
1  
@bummi That is an easy limitation to work around - use sys.all_columns or if your upper bound is ridiculously large use sys.all_objects CROSS JOIN sys.all_objects (or think about not using strings this way in the first place). – Aaron Bertrand Nov 2 '15 at 17:24

--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.