Extracting a string within a string between "[" and "]"

Question

I'm trying to extract a portion of a string. The string I want always begins with "[reportserver". I want to extract everything after that until the next "]" The text after "[reportserver" to "]" could be varied. Here is the entire string:

CREATE TRIGGER [dbo].[Schedule_UpdateExpiration] 
            ON [dbo].[Schedule] AFTER UPDATE  
AS
UPDATE EC
SET AbsoluteExpiration = I.NextRunTime 
FROM [reportserver - EMPTYTempDB].dbo.ExecutionCache AS EC
INNER JOIN ReportSchedule AS RS ON EC.ReportID = RS.ReportID
INNER JOIN inserted AS I ON RS.ScheduleID = I.ScheduleID AND RS.ReportAction = 3  

In this case I want to extract: "reportserver - EMPTYTempDB"

I've come close with this:

SELECT SUBSTRING(@TEXT,CHARINDEX('[reportserver',@TEXT)+1,(CHARINDEX(']',@TEXT)))

Which returns: "reportserver - EMPTYTe" . Close, but no cigar. What am I missing?

Answer 1

Upvoted the other answer because it's correct, but just a slightly different way to look at it - I like to use a CTE to pass down things like CHARINDEX so the code only has to include them once.

;WITH x AS 
(
  SELECT t = @text, c = CHARINDEX(N'[reportserver', @text)
),
y AS 
(
  SELECT r = SUBSTRING(t,c,4000) FROM x
),
z AS 
(
  SELECT o = LEFT(r, CHARINDEX(N']', r)) FROM y
)
SELECT o FROM z;

The nice thing about this approach (other than avoiding repeating any expression multiple times) is the ability to easily inspect the outcome of any CTE.

db<>fiddle