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

;
