joomla/href.sql

-- select all text position with href=" in it
-- look in content introText and fullText and in categories description
--

select ty, id, f, cx, tit, r
from (
  select ty, id, left(title, 30) tit, f, cx, substr(t, cx, locate('"', t, cx+6) + 1 - cx) r
    from ( select ty, id, title, f, 1 + c *10 + d c  -- occurence or hRef
        , t
        ,  char_length(substring_index(t, 'href="', 1 + c *10 + d)) + 1 cx -- position of occurrence
        from ( select 'con' ty, 'int' f, id, title, introText t from spWaJ34_content 
               union all select 'con' ty, 'ful' f,  id, title, `fullText` t from spWaJ34_content 
               union all select 'cat' ty, 'des' f,  id, title, `description` t from spWaJ34_categories
        ) t
        ,( -- count from 0 to 4
            select 0 c
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4
              ) c
        ,( -- count from 0 to 9
            select 0 d
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4
            union all select 5
            union all select 6
            union all select 7
            union all select 8
            union all select 9
              ) d
) s
    where cx > 0 and cx < char_length(t)
)r
where r like 'href="files/%' or r like 'href="regi%'
order by ty, id, f, cx 
;
        -- f <> 'int'
    -- r not like 'href="index.php%'
    -- and r not like 'href="http%'
    -- and r not like 'href="files/%'
    -- and r not like 'href="images/%'
    -- and r not like 'href="mailto:%'
        -- r like 'href="ima%'

;