Skip to main content
3 of 4
added 18 characters in body; edited title
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

How to find all positions of a string within another string

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
Ario
  • 1.1k
  • 4
  • 14
  • 27