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
I think this will be slightly more efficient than the looping method you've chosen (some evidence here), 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)+1)
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.
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
--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
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.
This is a simple code based on Aaron's answer that:
CODE:
DECLARE @termToFind CHAR(1) = 'X'
DECLARE @string VARCHAR(40) = 'XX XXX X XX'
SET @string += '.' --Add any data here (different from the one searched) to get the position of the last character
DECLARE @stringLength BIGINT = len(@string)
SELECT pos = Number - LEN(@termToFind)
FROM (
SELECT Number
, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, CHARINDEX(@termToFind, @string + @termToFind, Number) - Number)))
FROM (
--All numbers between 1 and the lengh of @string. Better than use sys.all_objects
SELECT TOP (@stringLength) row_number() OVER (
ORDER BY t1.number
) AS N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) AS n(Number)
WHERE Number > 1
AND Number <= CONVERT(INT, LEN(@string))
AND SUBSTRING(@termToFind + @string, Number, LEN(@termToFind)) = @termToFind
) AS y
RESULT
pos
--------------------
1
2
4
5
6
9
13
14
(8 row(s) affected)
Sorry guys to drop in so late, but I'd like to make things easier for people that want to expand on this. I was looking at each of these implementations, took the one that seemed the best to me (Aaron Bertrand), simplified it and here you go, you have the "template". Use it wisely.
CREATE FUNCTION dbo.CHARINDICES (
@search_expression NVARCHAR(4000),
@expression_to_be_searched NVARCHAR(MAX)
) RETURNS TABLE AS RETURN (
WITH tally AS (
SELECT Number = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects)
SELECT DISTINCT n = subIdx -- (4) if we don't perform distinct we'll get result for each searched substring, and we don't want that
FROM
tally
CROSS APPLY (SELECT subIdx = CHARINDEX(@search_expression, @expression_to_be_searched, Number)) x -- (2) subIdx is found in the rest of the substring
WHERE
Number BETWEEN 1 AND LEN(@expression_to_be_searched) -- (1) run for each substring once
AND SubIdx != 0 -- (3) we care only about the indexes we've found, 0 stands for "not found"
)
SELECT CHARINDEX('C', 'BACBABCBABBCBACBBABC')
SELECT * FROM dbo.CHARINDICES('C', 'BACBABCBABBCBACBBABC')
Just as a reference - you can derive other behaviors from this, like expand on PATINDEX():
CREATE FUNCTION dbo.PATINDICES (
@search_expression NVARCHAR(4000) = '%[cS]%',
@expression_to_be_searched NVARCHAR(MAX) = 'W3Schools.com'
) RETURNS TABLE AS RETURN (
WITH tally AS (
SELECT num = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects)
SELECT DISTINCT n = subIdx + num - 1
FROM
tally
CROSS APPLY (SELECT numRev = LEN(@expression_to_be_searched) - num + 1) x
CROSS APPLY (SELECT subExp = RIGHT(@expression_to_be_searched, numRev)) y
CROSS APPLY (SELECT subIdx = PATINDEX(@search_expression, subExp)) z
WHERE
num BETWEEN 1 AND LEN(@expression_to_be_searched)
AND SubIdx != 0
)
SELECT PATINDEX('%[cS]%', 'W3Schools.com')
SELECT * FROM dbo.PATINDICES('%[cS]%', 'W3Schools.com')
Declare @search varchar(5)
sET @search='a'
Declare @name varchar(40)
Set @name='AmitabhBachan'
Declare @init int
Set @init=1
Declare @hold int
Declare @table table (POSITION Int)
While( @init<= LEn(@name))
Begin
Set @hold=(Select CHARINDEX(@search,@name,@init))
If (@hold!=0)
BEgin
--Print @hold
Insert into @table
Select @hold
Set @init=@hold+1
End
Else
If (@hold=0)
BEgin
Break
End
End
Select * from @table
