How to select a specific line in a row with carriage returns

Question

Using SQL Server 2012 I'm running the following basic query

SELECT  MacroID
      ,MacroText
      ,AuditEventID
      ,AuditTypeCode
      ,AuditTimestamp
  FROM APXFirm.dbo.AdvMacro

However it's come to my attention that every result row has numerous carriage returns embeded, and I need a specific row within the rows.

Below is how the result set looks in SQL.

However when pasting it in Notepad it'll look something like this.

[Version] 3.5.1.212
[Description] TaxLot information for populating Holdings table
[Portfolios] @master
[Mode] Management
[AutoPrint] No
[Graph] No
[Sheet] No
[Attended] No
[Apply Copies] No
[Page Number By] Report
[Number One Page Reports] No
[Consolidate Composites] Both
[Frames] Yes
[Output File] TxLotExt
[Error File] TxLotExt
[Report]  txlotext.rep Report txlotext.rep
$prifile 123103
$_outfile TxLotExt.xml
gauge 2 1
prop MB
frame g1 2 c y y y "Report txlotext.rep" y - y y 0 0 100 100 000000 ffffff 0 1 "" ffffff 000000 360 0 0 6a240a n
frame g0 2 r n n y "Report txlotext.rep" y - y y 0 0 100 100 000000 ffffff 0 1 "" ffffff 000000 360 0 0 6a240a n

What I need is lines that begin with [Report] including the brackets, but I need the information that follows.

txlotext.rep Report txlotext.rep

I'm not sure how to query a row within a row.

Answer 1

SELECT  MacroID
      ,MacroText
      ,AuditEventID
      ,AuditTypeCode
      ,AuditTimestamp
     ,LTRIM(RTRIM(LEFT(
       RIGHT(MacroText,LEN(MacroText) - CHARINDEX('[Report] ',MacroText) - LEN('[Report] ')),
       CHARINDEX(CHAR(13),RIGHT(MacroText,LEN(MacroText) - CHARINDEX('[Report] ',MacroText) - LEN('[Report] '))) - 1
       ))) as DesiredLine
  FROM APXFirm.dbo.AdvMacro

Use a combination of CHARINDEX(), RIGHT(), LEFT(), and/or SUBSTRING() to find what you want and cut up the string. CHAR(13) is carriage return.