Retrieve specific data from database field

I have a table XPTO that contains the column REMARKS (data type ntext).

On the column REMARKS I have several rows.

My goal is to retrieve only the rows that has a specific text that starts with a specific word (@var1) and that ends with the next period punctuation mark (@var2).

I try this query but I only retrieve the fields that start with a specific word but that don't end with the following period punctuation mark (@var2):

For instance, let's say that I have several rows that show the text (Hi Laura! John will go to the Pub later. Will he take his girlfri开发者_如何学Goend?) and I just want this portion of text (John will go to the Pub later.).

DECLARE @var1 VARCHAR(1000)
DECLARE @var2 VARCHAR(1000)
SET @var1 = 'startword'
SET @var2 = '.'

SELECT CHARINDEX(@var1,REMARKS), SUBSTRING (REMARKS, CHARINDEX(@var1,REMARKS) ,500), * FROM XPTO WHERE REMARKS LIKE '%' + @var1 + '%' + @var2

---

Try this

declare @string varchar(max)
declare @var1 varchar(1000) 
declare @var2 varchar(1000) 
set @var1 = 'john' 
set @var2 = '.'

set @string='hi laura! john will go to the pub later. will he take is girlfriend?'
select SUBSTRING(string,1,charindex(@var2,string)-1) from
(
select SUBSTRING(@string,CHARINDEX(@var1,@string),len(@string )) as string
) as t

---

You can try below as well.

Select CHARINDEX(@var1,REMARKS), 
SUBSTRING (REMARKS, CHARINDEX(@var1,REMARKS) ,500), * 
From XPTO where REMARKS like @var1 + '%' and REMARKS like '%' + @var2

---

    DECLARE @var1 VARCHAR(1000)
    DECLARE @var2 VARCHAR(10)

    SET @var1 = 'string_to_be_found'
    SET @var2 = 'end_string'

    SELECT SUBSTRING(
    REMARKS
    ,CHARINDEX(@var1,REMARKS)
    , CHARINDEX(@var2,REMARKS,CHARINDEX(@var1,REMARKS))+ LEN(@var2) - CHARINDEX(@var1,REMARKS)
    )
    FROM XPTO
    WHERE CHARINDEX(@var1,REMARKS)>0
AND CHARINDEX(@var2,REMARKS,CHARINDEX(@var1,REMARKS))>0