Patindex and Charindex give me more than what i'm Asking for

Question

I have a string:

ALIS Predictions Y12 2016-17 Test Based Predictions

I'd like to return the number after the Y and have the following SQL (as an example):

SELECT SUBSTRING('ALIS Predictions Y12 2016-17 Test Based Predictions',
                 PATINDEX('%[0-9]%',
                          'ALIS Predictions Y12 2016-17 Test Based Predictions'),
                 CHARINDEX(' ',
                           'ALIS Predictions Y12 2016-17 Test Based Predictions'
                          )
                )

But the result I get is:

12 20

Surely the final CHARINDEX should be giving me the expression until the first space? How can I tweak it so that I'm only getting the numbers after the Y?

Answer 1

Just evaluate each section on it's own to see what is happening:

SELECT PATINDEX('%[0-9]%', 'ALIS Predictions Y12 2016-17 Test Based Predictions'),
       CHARINDEX(' ', 'ALIS Predictions Y12 2016-17 Test Based Predictions')

Which gives you 19 and 5 respectively, so you are telling the substring function to start at character 19, and take the next 5 characterss

I think what you really want to do, is find the first space after the start string, so you need to pass a third argument to CHARINDEX to specify the position to start. For the sake of clarity, since the start position needs to be used a few times, I have moved it into an APPLY so I can reuse an alias, rather than repeat the PATINDEX expression a number of times.

SELECT  SUBSTRING(t.Value, p.StartIndex, CHARINDEX(' ', t.Value, p.StartIndex) - p.StartIndex)
FROM    (SELECT 'ALIS Predictions Y12 2016-17 Test Based Predictions') AS t (Value)
        CROSS APPLY (SELECT PATINDEX('%Y[0-9]%', t.Value) + 1) AS p (StartIndex)