sql server 2008: select substring from a field

here is example data:

 10914_Excel Short Summary.xls
F:\MassHunter\DATA\10921_PAIN\QuantResults\10921_PAIn.batch.bin
10918_Excel Short Summary.xls
10923_Excel Short Summary.xls
10922_Excel Short Summary.xls
10913_Excel Short Summary.xls
10912R_Excel Short Summary.xls
F:\MassHunter\DATA\10907_PAIN\QuantResults\10907_PAIN.batch.bin
F:\MassHunter\DATA\10915_PAIN\QuantResults\10915_PAIN.batch.bin
F:\MassHunter\DATA\10909_PAIN\QuantResults\10909_PAIN.batch.bin
F:\MassHunter\DATA\10914_PAIN_new\QuantResults\10914_PAIN_new.batch.bin
F:\MassHunter\DATA\10911_PAIN\QuantResults\10911_PAIN.batch.bin
C:\Documents and Settings\Michelle Miller\Desktop\10917_PAIN\QuantResults\10917_PAIN.batch.bin
F:\MassHunter\DATA\10913_PAIN\QuantResults\10913_PAIN.batch.bin
C:\Sergey\10923_PAIN\QuantResults\10923_pain.batch.bin
C:\Documents and Settings\Michelle Miller\Desktop\10922_PAIN\QuantResults\10922_PAIN.batch.bin
C:\Sergey\10919_PAIN\QuantResults\10919_pain.batch.bin
D:\Marina\10920_PAIN\QuantResults\10920_PAIN.batch.bin
D:\Marina\10925_PAIN\QuantResults\10925_PAIN.batch.bin
D:\Marina\10937_PAIN\QuantResults\10937_PAIN.batch.bin
10925_Excel Short Summary.xls
10924_Excel Short Summary.xls
10925_Excel Short Summary_100727092302.xls
10925_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10924_PAIN\QuantResults\10924_pAIN.batch.bin
10924_THC_Excel Short Summary.xls
10926_Excel Short Summary.xls
10927_Excel Short Summary.xls
10928_Excel Short Summary.xls
10929_Excel Short Summary.xls
10930_Excel Short Summary.xls
10931_Excel Short Summary.xls
10932_Excel Short Summary.xls
10933_Excel Short Summary.xls
10934_Excel Short Summary.xls
10935_Excel Short Summary.xls
10936_Excel Short Summary.xls
F:\MassHunter\DATA\10926_PAIN\QuantResults\10926_PAIN.batch.bin
F:\MassHunter\DATA\10938_PAIN\QuantResults\10938_Pain.batch.bin
F:\MassHunter\DATA\10928_PAIN\QuantResults\10928_PAIN.batch.bin
C:\LSR Data\10940_PAIN\QuantResults\10940_PAIN.batch.bin
10927_THC_Excel Short Summary.xls
10930_THC_Excel Short Summary_100727112433.xls
F:\MassHunter\DATA\10932_PAIN\QuantResults\10932_PAIN.batch.bin
10926_THC_Excel Short Summary_100727113358.xls
10929_THC_Excel Short Summary.xls
10928_THC_Excel Short Summary.xls
10931_THC_Excel Short Summary.xls
10932_THC_Excel Short Summary_100727114507.xls
F:\MassHunter\DATA\10939_PAIN\QuantResults\10939_PAIN.batch.bin
10947_Excel Short Summary.xls
10948_Excel Short Summary.xls
10949_Excel Short Summary.xls
10933_THC_Excel Short Summary.xls
10934_THC_Excel Short Summary.xls
10937_Excel Short Summary.xls
10938_Excel Short Summary.xls
10939_Excel Short Summary.xls
10942_Excel Short Summary.xls
F:\MassHunter\DATA\10927_PAIN\QuantResults\10927_PAIN.batch.bin
10940_Excel Short Summary.xls
F:\MassHunter\DATA\10931_PAIN\QuantResults\10931_PAIN.batch.bin
F:\MassHunter\DATA\10929_PAIN\QuantResults\10929_PAIN.batch.bin
10935_THC_Excel Short Summary.xls
10937_THC_Excel Short Summary.xls
10938_THC_Excel Short Summary.xls
10950_Excel Short Summary.xls
F:\MassHunter\DATA\10941_PAIN\QuantResults\10941_PAIN.batch.bin
10947_THC_Excel Short Summary.xls
10949_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10934_PAIN\QuantResults\10934_PAIN.batch.bin
10950_THC_EXCEL SHORT SUMMARY.XLS_Final.xls
10940_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10935_PAIN\QuantResults\10935_PAIN.batch.bin
F:\MassHunter\DATA\10942_PAIN\QuantResults\10942_PAIN.batch.bin
F:\MassHunter\DATA\10933_PAIN\QuantResults\10933_PAIN.batch.bin
10941_Excel Short Summary.xls
10939_THC_Excel Short Summary.xls
10941_THC_Excel Short Summary.xls
10942_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10936_PAIN\QuantResults\10936_PAIN.batch.bin
F:\MassHunter\DATA\10950_PAIN\QuantResults\10950_PAIN.batch.bin
F:\MassHunter\DATA\10949_PAIN\QuantResults\10949_PAIN.batch.bin
10950_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10930_PAIN\QuantResults\10930_PAIN.batch.bin
F:\MassHunter\DATA\10948_PAIN\QuantResults\10948_PAIN.batch.bin
10936_THC_Excel Short Summary.xls
10948_THC_Excel Short Summary.xls
10966_THC_Excel Short Summary.xls
10964_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10947_R_PAIN\QuantResults\10947_R_PAIN.batch.bin
10960_THC_Excel Short Summary.xls
10958_Excel Short Summary.xls
10963_THC_Excel Short Summary.xls
10962_THC_Excel Short Summary.xls
10959_Excel Short Summary.xls
10961_THC_Excel Short Summary.xls
10960_Excel Short Summary.xls
10959_THC_Excel Short Summary.xls
10961_Excel Short Summary.xls
10962_Excel Short Summary.xls
10963_Excel Short Summary.xls
10967_THC_Excel Short Summary.xls
10964_Excel Short Summary.xls
10968_THC_Excel Short Summary.xls
10965_Excel Short Summary.xls
10969_THC_Excel Short Summary.xls
10970_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10958_PAIN_new\QuantResults\10958_PAIN.batch.bin
F:\MassHunter\DATA\10964_PAIN\QuantResults\10964_PAIN.batch.bin
F:\MassHunter\DATA\10966_PAIN\QuantResults\10966_PAIN.batch.bin
F:\MassHunter\DATA\10981_PAIN\QuantResults\10981_PAIN.batch.bin
10958_THC_Excel Short Summary.xls
10965_THC_Excel Short Summary.xls
F:\MassHunter\DATA\10963_PAIN\QuantResults\10963_PAIN.batch.bin
10971_THC_Excel Short Summary.xls
10972_THC_Excel Short Summary.xls
10966_Excel Short Summary.xls
F:\MassHunter\DATA\10961_PAIN\QuantResults\10961_PAIN.batch.bin
10967_Excel Short Summary.xls
F:\MassHunter\DATA\10962_PAIN\QuantResults\10962_PAIN.batch.bin
10968_Excel Short Summary.xls
F:\MassHunter\DATA\10965_PAIN\QuantResults\10965_PAIN.batch.bin
10969_Excel Short Summary.xls
10970_Excel Short Summary.xls

the result that i need is:

how would this select statement look?

select column from table where column...

Break it down into 2 steps:

discard everything upto the start of the number (here I assumed 3 digists minimum)
then take everything upto the next non-numeric digit

You'll need a CASE for the LEFT is the number is at the end because PATINDEX will return zero

DECLARE @MyTable TABLE (bigstring varchar(200))
INSERT @MyTable VALUES ('F:\MassHunter\DATA\6897_Pan_1\QuantResults\6897_Pan_1.batch.bin')
INSERT @MyTable VALUES ('F:\MassHunter\DATA\6897_Pan_1\QuantResults\6897_Pan_1.batch.bin')
INSERT @MyTable VALUES ('10914_Excel Short Summary.xls')

SELECT  --assumes number not at end of string
    LEFT(startOf, PATINDEX('%[^0-9]%', startof)-1)
FROM
    (
    SELECT  --assumed 3 digits minimum
        SUBSTRING(bigstring, PATINDEX('%[0-9][0-9][0-9]%', bigstring), 8000) AS startOf
    FROM
        @MyTable
    ) foo