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
        descr, charindex(',', descr) position
        from facility
        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) > 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3) 
    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)
    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.

| improve this answer | |

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)
| improve this answer | |

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
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

 IF LEN(@sItem) > 0

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

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:

(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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.