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