MODELNUMBER=Z12345&HELLOWORLD=WY554&GADTYPE=PLA&ID=Z-12345
/DTYPE=PLA&ID=S-10758&UN_JTT_REDIRECT=UN_JTT_IOSV

Z-12345
Z-12345&UN_JTT_REDIRECT=UN_JTT_IOSV

Z-12345
Z-12345

SUBSTRING(MyCol, (PATINDEX('%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%',[MyCol])),7)

-- from Mytable
WHERE [MyCol] like '%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%'

select
    substring(d.data, s.s, isnull(nullif(e.e,0),2000)-s.s) as ID,
    d.data
from data d
cross apply (
    select charindex('&ID=', d.data)+4 as s
) s
cross apply (
    select charindex('&', d.data, s) as e
) e
where s.s > 4