log in

SA extract thread references

Luke Breuer
2011-01-05 22:46 UTC

with M as (
    select  po.*,
            r = dbo.RegexMatchValue(text_nonquote, '(?<=http://forums.somethingawful.com.{1,100}\?threadid=)\d+'),
            rn = row_number() over (partition by dbo.RegexMatchValue(text_nonquote, '(?<=http://forums.somethingawful.com.{1,100}\?threadid=)\d+') order by post_pk)
    from    Post po
    inner join Person on person_pk = po.input_person_fk
    where   thread_fk in (1,2)
        and username = 'Victor'
select  t.name, r, dbo.RegexMatchValue(text_nonquote, 'http://forums.somethingawful.com[^"]*')
from    M
left join Thread t on thread_pk = thread_fk
where   r is not null
    and rn < 4