Myujth

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!

You can use apply and string functions:

select left(v.s1, charindex(' ', s1) - 1)
from t cross apply
 (values (stuff(t.col, 1, 11, '')) v(s1) 

Here is an alternative to Gordon's answer:

SELECT
 SUBSTRING(ColumnName,
 CHARINDEX(':', ColumnName) + 2,
 CHARINDEX(' ', ColumnName, CHARINDEX(':', ColumnName) + 2) -
 CHARINDEX(':', ColumnName) - 2) AS Name
FROM yourTable;

Demo

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 

Here is your data to test for in table form:

declare @goals table (string nvarchar(255));
insert @goals values 
 ('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 
 (' '); -- I (psw) added this

And the following code seems to do what you desire without error. But it is assuming that your sentence after the name will always start with "Goals".

select *,
 result = 
 case 
 when isValid = 1 then 
 ltrim(rtrim(
 substring(string, colonPos + 1, goalsPos - colonPos - 1)
 ))
 end

from @goals
cross apply (select 
 colonPos = charindex(':', string),
 goalsPos = patIndex('%goals%', string)
 ) positions
cross apply (select
 isValid = 
 case 
 when colonPos = 0 or goalsPos = 0 or colonPos > goalsPos then 0
 else 1
 end
 ) validity