Charindex, Getting specific data from a string Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error.  I searched for similar problems on stackexchange as well as a few other sites and it seems I am still unable to fully grasp the concept to get what I need.

My biggest issue is the data that I have in the column is not in any set pattern.  I found a stackexchange question that answered this but my setup is a bit different.  Here are a few examples from column descr and table facility:

ID | Descr |
21 | Playhouse, Virginia Series, 98 Stage, PSDD, House|
35 | Playhouse, Virginia Series, 111 Stage, Inconel|
53 | Playhouse, FX Series, 125 Stage, House F31|
76 | FX Series, 134 Stage, F31, Onconel|


A bit more information.  There should always be a comma before the number I am looking for.  I have found one case out of thousands that this isn't the case, but to make this easier we will assume it always has a leading comma and space.  The number I am looking for can be either two or three digits.  It is also always followed by a space and the word stage.

Here is what I attemped but received and error:
select

with cterecords(descr, position) as
(
    select
        descr, charindex(',', descr) position
        from facility
)
    select
        substring(descr, position +1,
        charindex('stage', descr,position) - position -1)
    from cterecords;


I am receiving this error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

But if I get through the error, I am not sure if I have the query written to get exactly what I want.

Here is SQL Fiddle with test data: SQL Fiddle Example Data
I use charIndex and patIndex to resolve it.

CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) &gt; 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))&gt;=3
    THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3) 
    ELSE NULL END
    AS NewNo,   
CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) &gt; 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))&gt;=3
    THEN SUBSTRING(Descr, PatIndex('%, [0-9][0-9]% Stage%',Descr)+2,3)
    ELSE NULL END
    AS NewNo2   


With the output:

NewNo NewNo2
----- ------
 98   98 
111   111
125   125
134   134


Some explanation on it:
I used to CharIndex(' Stage', Descr)to find the position when starts ' Stage'.
Then I use SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1) to cut the text so that your number will be in the right part. (like this :Playhouse, Virginia Series, 98).Then you can use different technique (right,another charIndex,reverse) to obtain the Number.