How to select a variable length string from between two known strings in SQL Server in a VARCHAR(MAX) where some columns don't have applicable strings

Question

Using SQL Server 2012, I need to get from this example

ColumnName    
--------------------------------
 Enroll to: Carol Goals are many and varied  
 characters that don't include desired results
 Enroll to: Jan Levinson Goals will be discussed at first encounter
 Enroll to: Stephon-Anderson   Goals none
 NULL
 Enroll to:   David   Goals  --Note uneven spaces, Need David 

to extract the column to look like:

Name    
-----------
Carol
NULL   
Jan Levinson   
Stephon-Anderson 
NULL 
David 

This code got me pretty close to the results I was looking for, but sometimes trimmed the name incorrectly.

Select 
CASE WHEN AssignedTo like '%Enroll To:%' THEN  SUBSTRING(AssignedTo, CHARINDEX('%Enroll To:%', AssignedTo) + LEN('%Enroll To:%') 
            ,CHARINDEX('Goals', AssignedTo) - CHARINDEX('%Enroll To:%', AssignedTo) + LEN('Goals'))
          ELSE 'None'
          END AS 'Name'
FROM
(

  Select 
CASE WHEN ColumnName like '%Enroll To:%' THEN SUBSTRING    (ColumnName, CHARINDEX('Enroll To:', ColumnName), 40) 


ELSE 'None'
END AS 'AssignedTo'

FROM TABLE ) A

I cannot thank you enough!

Answer 1

This produced the desired result and seems to deal with variable length of the target string. Hope it helps someone.

DECLARE @pretext as NVARCHAR(100) = 'Enroll to:' 
DECLARE @posttext as NVARCHAR(100) = 'Goals'

Select 
,CASE When CHARINDEX(@posttext, ColumnName) - (CHARINDEX(@pretext, ColumnName) + len(@pretext)) < 0 THEN NULL
    Else
    SUBSTRING(ColumnName, CHARINDEX(@pretext, ColumnName) + len(@pretext)
    ,CHARINDEX(@posttext, ColumnName) - (CHARINDEX(@pretext, ColumnName) + len(@pretext)) )    
    END as betweentext 

FROM TABLE