PatIndex Pattern /Regex: How to match a dot followed by a space

Question

Q1-PatIndex Pattern Regex: How to match a dot followed by a space? Q2 -PatIndex Pattern Regex: How to match a dot followed by two spaces?.

I want to put it in here to get only the GOAL content

 Declare @Temp Table(Data VarChar(1000))

Insert Into @Temp Values('Lalallaa GOAL: This is the truthmeow. Meow.  ')
Insert Into @Temp Values('Lalallaa GOAL: This is the truth. Meowrwr. ')
Insert Into @Temp Values('lALALLA GOAL: This is the truth. Meowrwr.  NOTINCLUDED: WAWAW')


Select Left(
             SubString(Data,PATINDEX ('%GOAL%',Data), 8000) ,
             PatIndex('regex here', SubString(Data, PatIndex('%[GOAL]%', Data), 8000))-1)
From   @Temp

Expected Output

GOAL: This is the truthmeow. 
GOAL: This is the truth.
GOAL: This is the truth. 

I used Shnugos answer on the real DB and I encountered an error : Illegal name character

I checked the data type , it is ntext

Answer 1

What about this:

Short explanation: Replacing . with xml-tags will "split" this string in as many "parts" as there are in your string. The XML-value method will take the first item's value which is the string up to the first .

Declare @Temp Table(Data VarChar(1000))

Insert Into @Temp Values('Lalallaa GOAL: This is the truthmeow. Meow.  ')
Insert Into @Temp Values('Lalallaa GOAL: This is the truth. Meowrwr. ')
Insert Into @Temp Values('lALALLA GOAL: This is the truth. Meowrwr. NOTINCLUDED: WAWAW')


Select CAST('<x>' + REPLACE(SubString(Data,PATINDEX ('%GOAL%',Data), 8000),'. ','</x><x>') + '</x>' AS XML).value('x[1]','varchar(max)')
From   @Temp    

Answer 2

You can try to use the LIKE statement as well.

columnname LIKE '.  %'

EDIT:

Try this:

Select SUBSTRING(data, LEN(LEFT(data, CHARINDEX ('GOAL:', data))) , LEN(data) - LEN(LEFT(data, CHARINDEX ('GOAL:', data))) - LEN(RIGHT(data, LEN(data) - CHARINDEX ('.', data))) - 1)
From   @Temp