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?

Gordon Linoff · Answer 1 · 2016-11-04T10:51:57.877

0

The problem is that the third argument is looking at the first space in the string, not the one after "Y". Here is another way:

SELECT LEFT(numstr, CHARINDEX(' ', x.str) - 1)
FROM (SELECT 'ALIS Predictions Y12 2016-17 Test Based Predictions' as str
     ) x OUTER APPLY
     (SELECT STUFF(x.str, 1, PATINDEX('%[0-9]%', x.str) - 1, '') as numstr
     ) y;

edited Nov 04 '16 at 10:51

answered Nov 04 '16 at 09:42

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon but I was unable to run the SQL – Imran Nov 04 '16 at 10:43

score 0 · Accepted Answer · answered Nov 04 '16 at 09:59

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)

Thanks @GarethD. I understand now. This works really well. – Imran Nov 04 '16 at 10:11 — Imran, Nov 04 '16 at 10:11

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

2 Answers2