SQL Server – Searching for X at the Beginning of a Line in Multi-Line String

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

I recently did an SSIS package that went though a table with over 4 billion records, the first step in the package was a query that extracted about 50 million records. The query just took a few seconds. Granted, my query was able to use an index, but it did have multiple conditions, and had to skip through the index to get at a subset of all the records and then test them with another couple conditions. And the return set was in the same order of magnitude as one of your entire tables. So I don't think you will have to do much to make this happen.

Sounds like you may not know how to use the LIKE operator. You can find details for that here. That will let you use some regular expressions to match against your text.

I'd recommend that you load the 2-grams into a table and give it a try. My guess is that a simple query with a LIKE condition will do the job for you.