0

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

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Philip Morris
  • 459
  • 1
  • 9
  • 26

2 Answers2

1

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
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
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
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • I got Lalallaa GOAL: This is the truthmeow, Lalallaa GOAL: This is the truth, lALALLA GOAL: This is the truth – Philip Morris Feb 12 '16 at 09:36
  • @PhilipMorris, that's easy, just use your own `substring` approach to cut off the beginning, edited my answer... – Shnugo Feb 12 '16 at 09:41
  • any chance it wont work with chinese characters or special characters? Im getting an error XML parsing: Illegal name character – Philip Morris Feb 12 '16 at 09:50
  • @PhilipMorris You probably must declare all your variable as **N**VARCHAR and put an "N" to all literally written strings (`N'Your string here'`) to make it unicode... – Shnugo Feb 12 '16 at 09:52
  • I confirmed and the type is ntext – Philip Morris Feb 12 '16 at 09:56
  • @PhilipMorris NTEXT is depricated, you might cast it to NVARCHAR(MAX). Btw: I answered your question **as it was written**. There was nothing mentioned about special characters. The right thing on SO is to close one question (with votes on all good or bad contributions and an accept) and than start a new question with the new problem. [Please read this, especially the point about *taking away the acceptance*](http://meta.stackoverflow.com/a/266768/5089204) – Shnugo Feb 12 '16 at 09:58
  • @PhilipMorris Thx :-) If you place a follow-up question it is a good idea to place a link (from the "share" option below your question) as a comment here (or include a link into your new question) I'll be happy to pop up there and help you if needed... – Shnugo Feb 12 '16 at 10:01