1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
WITH raw AS (
SELECT content AS html FROM read_text('page.html')
),
anchors AS (
SELECT unnest(regexp_extract_all(html, '<a [^>]*href="(https?://[^"]+)"[^>]*>(.*?)</a>', ['1','2'])) AS pair
FROM raw
),
split AS (
SELECT pair['1'] AS url, pair['2'] AS name FROM anchors
),
clean AS (
SELECT
trim(regexp_replace(url, '&', '&', 'g')) AS url,
trim(regexp_replace(regexp_replace(name, '<[^>]*>', '', 'g'), '&', '&', 'g')) AS name
FROM split
),
companies AS (
SELECT * FROM clean
WHERE url NOT LIKE '%sandiego.edu%'
AND url NOT LIKE '%facebook.com%'
AND url NOT LIKE '%twitter.com%'
AND name NOT SIMILAR TO '(Skip to content|Home|Request Info|Back to Blog.*|Computer Science|Engineering|Technology|Show Me.*|Apply Now|100 Most Influential.*|protect sensitive information|."Best Cybersecurity Company".|"Partner of the Year"|RELATED.*)'
AND length(name) BETWEEN 2 AND 60
AND url NOT LIKE '%time.com%' AND url NOT LIKE '%liaisoncas.com%' AND url NOT LIKE '%nordvpn.com%' AND url NOT LIKE '%excellence-awards.com%' AND url NOT LIKE '%careers.trellix.com%' AND url NOT LIKE '%cyderes.com/news-feed%'
)
SELECT name, url, regexp_replace(host, '^www\.', '') AS domain
FROM (
SELECT name, url2 AS url,
regexp_extract(url2, 'https?://([^/]+)', 1) AS host
FROM (
SELECT name,
CASE WHEN url LIKE '%herjavecgroup.com%' THEN 'https://www.cyderes.com/' ELSE url END AS url2
FROM companies
) a
) t
QUALIFY row_number() OVER (PARTITION BY regexp_replace(regexp_extract(url, 'https?://([^/]+)', 1), '^www\.', '') ORDER BY length(url)) = 1
ORDER BY name;
|
