Return specific text from MySQL using LOCATE and S

Question

I want to know how many yards were ran in my NFL SQL database. I use the following query:

Select season, description, down, ydsto1st
FROM nflpxp
WHERE 
(
   DESCRIPTION LIKE "%up the middle%" 
   OR DESCRIPTION REGEXP "(left|right) (tackle|guard|end)" 
   OR DESCRIPTION REGEXP " rushe(d|s) for "
)
AND season = 2012 AND off = 'WAS'
GROUP BY id

This returns

season  description down    ydsto1st
2012    (13:58) (Shotgun) R.Griffin right end to WAS 44 for 12 yards (S.Shanle).    2   10
2012    (11:23) A.Morris right tackle to NO 27 for 3 yards (C.Jordan).  1   10
2012    (10:44) (Shotgun) A.Morris right guard to NO 25 for 2 yards (S.Shanle; B.Bunkley).  2   7
2012    (8:30) (Shotgun) A.Morris right guard to NO 22 for 2 yards (W.Smith).   2   15

I want to add a column to my query that pulls out the number of yards run from "for nn yards"

I tried:

SELECT LEFT (description, LOCATE(' yards', description, 2)-1) AS yards FROM nflPxP

But it didn't work. Any help is appreciated.

Answer 1

Something like this should work;

SELECT 
      SUBSTRING_INDEX(
        LEFT(description, 
          GREATEST(LOCATE(' yards (', description),
                   LOCATE(' yard (',  description))-1), ' ', -1)
FROM nflPxP

An SQLfiddle to test with.

While this query works for a row that is fetched by some other search criteria, it's not indexable, so you may want to just add a separate column to store the data if searching the data is required.