SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text), CHARINDEX('immediately', @Text))

SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text)
, CHARINDEX('immediately',@text) - CHARINDEX('the dog', @Text) + Len('immediately'))

aaaaa$bbbbb$ccccc

SELECT SUBSTRING('aaaaa$bbbbb$ccccc',CHARINDEX('$','aaaaa$bbbbb$ccccc')+1, CHARINDEX('$','aaaaa$bbbbb$ccccc',CHARINDEX('$','aaaaa$bbbbb$ccccc')+1) -CHARINDEX('$','aaaaa$bbbbb$ccccc')-1) as My_String

bbbbb

declare @TEXT varchar(200)
declare @ST varchar(200)
declare @EN varchar(200)
set @ST = 'the dog'
set @EN = 'immediately'
set @TEXT = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'
SELECT SUBSTRING(@Text, CHARINDEX(@ST, @Text), (CHARINDEX(@EN, @Text)+LEN(@EN))-CHARINDEX(@ST, @Text))

SELECT SUBSTRING(@Text, CHARINDEX(@First, @Text) + LEN(@First),
                 CHARINDEX(@Second, @Text) - CHARINDEX(@First, @Text) - LEN(@First))

SELECT
SUBSTRING( '123@yahoo.com',  charindex('@','123@yahoo.com',1) + 1, charindex('.','123@yahoo.com',1) - charindex('@','123@yahoo.com',1) - 1 )

SELECT SUBSTRING(@TEXT, PATINDEX('%the dog%', @TEXT), PATINDEX('%immediately%',@TEXT))

DECLARE @Text VARCHAR(MAX), @First VARCHAR(MAX), @Second VARCHAR(MAX)
SET @Text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'
SET @First = 'the dog'
SET @Second = 'immediately'

SELECT SUBSTRING(@Text, CHARINDEX(@First, @Text),
                 CHARINDEX(@Second, @Text) - CHARINDEX(@First, @Text) + LEN(@Second))

DECLARE @text VARCHAR(MAX)
SET @text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'

DECLARE @start INT
SELECT @start = CHARINDEX('the dog',@text)

DECLARE @endLen INT
SELECT @endLen = LEN('immediately')

DECLARE @end INT
SELECT @end = CHARINDEX('immediately',@text)
SET @end = @end - @start + @endLen

SELECT @end

SELECT SUBSTRING(@text,@start,@end)

CREATE FUNCTION dbo.str_between(@col varchar(max), @start varchar(50), @end varchar(50))
    RETURNS varchar(max)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
        RETURN substring(@col, charindex(@start, @col) + len(@start),
          isnull(nullif(charindex(@end, stuff(@col, 1, charindex(@start, @col)-1, '')),0),
          len(stuff(@col, 1, charindex(@start, @col)-1, ''))+1) - len(@start)-1);
    END;
    GO

<pre>

SET @text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'

declare @pretext as nvarchar(100) = 'the dog'
declare @posttext as nvarchar(100) = 'immediately'

SELECT
 CASE
When CHARINDEX(@posttext, @Text) - (CHARINDEX(@pretext, @Text) + len(@pretext)) < 0 THEN
''
Else
SUBSTRING(@Text, CHARINDEX(@pretext, @Text) + len(@pretext)
,   CHARINDEX(@posttext, @Text) - (CHARINDEX(@pretext, @Text) + len(@pretext)) )
END as betweentext