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
Best Answer
I use
charIndex
andpatIndex
to resolve it.With the output:
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
,anothercharIndex
,reverse
) to obtain the Number.