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%'
;