Charindex, Getting specific data from a string

Question

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

Answer 1

I use charIndex and patIndex to resolve it.

CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=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) > 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=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.

Answer 2

Assuming that, even if there is no comma, at least a space character is always there before the sought number (when it is not at the beginning of Descr), here is what you could do:

1. Find the position of ' Stage':

   CHARINDEX(' Stage', Descr)
   

2. Cut the string at that position.

   STUFF(Descr, CHARINDEX(...), 999999999, '')
   

   If CHARINDEX finds nothing, it returns 0. When 0 is specified as the second parameter of STUFF, the result will be NULL, which should make sense, I assume.

3. Take the three rightmost characters from the remaining string.

   RIGHT(STUFF(...), 3)
   

   If the number is two digits, you will probably get a space character at the beginning, which, in my estimation, should be fine, as the result will still be easily convertible to a numeric type (if that is the ultimate goal).

   If the number is two digits and the nn Stage item is at the beginning of Descr, you will actually get only the two digits, without any space character, which should be even better.

So, putting everything together, this is the complete expression:

RIGHT(STUFF(Descr, CHARINDEX(' Stage', Descr), 999999999, ''), 3)

Answer 3

Here is a working example.

There is a very common function that gets used in SQL databases called fnSplit. It's not native, but I have seen it added to databases many times. It's a table-valued function that takes a string and returns a table (splitting the string on some delimiter).

The code for the function is below:

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList NVARCHAR(MAX) -- List of delimited items
  , @sDelimiter NVARCHAR(8) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item NVARCHAR(MAX))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
    @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

NOTE: I did not write fnSplit, the credit for that goes to deeptideshpande here.

Once you have that function, your problem is easy; just split the text on space and then just look for numbers:

DECLARE @Data TABLE (ID INT,Descr NVARCHAR(MAX))
INSERT INTO @Data
VALUES
(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')

SELECT      *
FROM        @Data AS D
OUTER APPLY dbo.fnSplit(D.Descr,' ') AS S
WHERE       ISNUMERIC(S.item) = 1