Fork of List of URL with invalid Top Level Domain limit 10 by
This query is marked as a draft This query has been published by .

SQL

AخA
 
WITH p as (
  SELECT page.page_id, page.page_title 
  FROM page 
  WHERE page.page_namespace =0
) 
, TLDh as (
  SELECT DISTINCT e.el_to_domain_index, SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) as TLD
  FROM externallinks as e INNER JOIN p
  ON p.page_id = e.el_from
  WHERE  LEFT(e.el_to_domain_index,4) ='http' 
    AND SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) NOT IN ('abb',
'abbott',
'abudhabi',
'ac',
'academy',
'aco',
'actor',
'ad',
'adult',
'ae',
'aero',
'af',
'africa',
'ag',
'agency',
'ai',
'akdn',
'al',
'alsace',
'am',
'amsterdam',
'ao',
'app',
'aq',
'ar',
'archi',
'army',
'art',
'as',
'asia',
'at',
'attorney',
'au',
'auction',
'audi',
'audio',
'auto',
'aw',
'ax',
'az',
'ba',
'band',
'bank',
'bar',
'barcelona',
'barclays',
'basketball',
'bayern',
'bb',
'bd',
'be',
'beer',
'berlin',
'best',
'bet',
'bf',
'bg',
'bh',
'bi',
'bible',
'bid',
'bike',
'bio',
'biz',
'bj',
'black',
'blog',
'blue',
'bm',
'bn',
'bnpparibas',
'bo',
'bot',
'boutique',
'br',
'bradesco',
'brussels',
'bs',
'bt',
'business',
'buzz',
'bw',
'by',
'bz',
'bzh',
'ca',
'cab',
'cafe',
'cam',
'camera',
'camp',
'canon',
'capetown',
'capital',
'car',
'cards',
'care',
'careers',
'cars',
'cash',
'cat',
'catering',
'cc',
'cd',
'center',
'cern',
'cf',
'cfa',
'cg',
'ch',
'charity',
'chat',
'church',
'ci',
'city',
'ck',
'cl',
'click',
'clinic',
'cloud',
'club',
'cm',
'cn',
'co',
'coach',
'codes',
'coffee',
'cologne',
'com',
'community',
'company',
'construction',
'consulting',
'cooking',
'cool',
'coop',
'corsica',
'country',
'cr',
'cricket',
'crs',
'cruises',
'cu',
'cv',
'cw',
'cx',
'cy',
'cymru',
'cyou',
'cz',
'dance',
'date',
'de',
'dental',
'design',
'dev',
'dhl',
'diamonds',
'digital',
'direct',
'directory',
'discount',
'dj',
'dk',
'dm',
'do',
'doctor',
'dog',
'domains',
'download',
'dvag',
'dz',
'earth',
'ec',
'eco',
'edeka',
'edu',
'education',
'ee',
'eg',
'email',
'emerck',
'energy',
'engineering',
'er',
'es',
'estate',
'et',
'eu',
'eus',
'events',
'exchange',
'expert',
'exposed',
'express',
'fage',
'fail',
'faith',
'family',
'farm',
'fashion',
'fi',
'film',
'finance',
'fish',
'fishing',
'fit',
'fitness',
'fj',
'fk',
'flights',
'fm',
'fo',
'football',
'foundation',
'fr',
'frl',
'fun',
'fund',
'furniture',
'futbol',
'fyi',
'ga',
'gal',
'gallery',
'game',
'games',
'garden',
'gay',
'gd',
'ge',
'gent',
'gf',
'gg',
'gh',
'gi',
'gift',
'gl',
'glass',
'global',
'gm',
'gmbh',
'gn',
'gold',
'golf',
'goog',
'google',
'gop',
'gov',
'gp',
'gq',
'gr',
'graphics',
'gratis',
'green',
'group',
'gs',
'gt',
'gu',
'guide',
'guitars',
'guru',
'gw',
'gy',
'hamburg',
'haus',
'health',
'help',
'hiv',
'hk',
'hm',
'hn',
'hockey',
'honda',
'horse',
'host',
'hosting',
'house',
'how',
'hr',
'ht',
'hu',
'icu',
'id',
'ie',
'ikano',
'il',
'im',
'imdb',
'immo',
'immobilien',
'in',
'inc',
'industries',
'info',
'ink',
'institute',
'int',
'international',
'invalid',
'investments',
'io',
'iq',
'ir',
'irish',
'is',
'ismaili',
'ist',
'istanbul',
'it',
'je',
'jetzt',
'jm',
'jo',
'jobs',
'joburg',
'jp',
'kaufen',
'ke',
'kg',
'kh',
'ki',
'kim',
'kitchen',
'kiwi',
'km',
'kn',
'koeln',
'komatsu',
'kp',
'kpmg',
'kpn',
'kr',
'krd',
'kw',
'ky',
'kz',
'la',
'land',
'lat',
'law',
'lawyer',
'lb',
'lc',
'leclerc',
'legal',
'lgbt',
'li',
'lidl',
'life',
'lighting',
'limited',
'link',
'live',
'lk',
'local',
'lol',
'london',
'love',
'lr',
'ls',
'lt',
'ltd',
'lu',
'luxury',
'lv',
'ly',
'ma',
'madrid',
'man',
'management',
'market',
'marketing',
'markets',
'mba',
'mc',
'md',
'me',
'media',
'melbourne',
'memorial',
'men',
'menu',
'mg',
'mil',
'mk',
'ml',
'mm',
'mn',
'mo',
'mobi',
'moe',
'money',
'monster',
'moscow',
'movie',
'mp',
'mq',
'mr',
'ms',
'mt',
'mu',
'museum',
'mv',
'mw',
'mx',
'my',
'mz',
'na',
'nagoya',
'name',
'nc',
'ne',
'net',
'network',
'neustar',
'news',
'nf',
'ng',
'ngo',
'ni',
'ninja',
'nl',
'no',
'np',
'nr',
'nrw',
'ntt',
'nu',
'nyc',
'nz',
'observer',
'okinawa',
'om',
'one',
'ong',
'onion',
'onl',
'online',
'ooo',
'org',
'ovh',
'pa',
'page',
'panasonic',
'paris',
'partners',
'parts',
'party',
'pe',
'pet',
'pf',
'pg',
'ph',
'photo',
'photography',
'photos',
'pics',
'pictet',
'pictures',
'pink',
'pioneer',
'pk',
'pl',
'place',
'plus',
'pm',
'pn',
'post',
'pr',
'press',
'pro',
'productions',
'properties',
'property',
'ps',
'pt',
'pub',
'pw',
'py',
'qa',
'quebec',
'racing',
'radio',
're',
'realestate',
'recipes',
'red',
'reise',
'reisen',
'report',
'rest',
'restaurant',
'review',
'reviews',
'ricoh',
'rio',
'rip',
'ro',
'rocks',
'rs',
'ru',
'rugby',
'ruhr',
'run',
'rw',
'rwe',
'sa',
'saarland',
'sale',
'salon',
'sandvik',
'sb',
'sbi',
'sc',
'schmidt',
'school',
'schule',
'schwarz',
'science',
'soccer',                                                                                    
'scot',
'sd',
'se',
'security',
'sener',
'services',
'sexy',
'sg',
'sh',
'sharp',
'shop',
'shopping',
'show',
'si',
'site',
'sk',
'ski',
'sky',
'sl',
'sm',
'sn',
'sncf',
'so',
'social',
'softbank',
'software',
'solar',
'solutions',
'sony',
'space',
'sport',
'sr',
'ss',
'st',
'stada',
'stockholm',
'store',
'stream',
'studio',
'study',
'style',
'su',
'supply',
'support',
'surf',
'sv',
'swiss',
'sx',
'sy',
'systems',
'sz',
'taipei',
'tatar',
'tattoo',
'tax',
'tc',
'td',
'team',
'tech',
'technology',
'tel',
'tennis',
'test',
'tf',
'tg',
'th',
'theater',
'tips',
'tirol',
'tj',
'tk',
'tl',
'tm',
'tn',
'to',
'today',
'tokyo',
'tools',
'top',
'toshiba',
'total',
'tours',
'town',
'toyota',
'tr',
'training',
'travel',
'trust',
'trv',
'tt',
'tube',
'tv',
'tw',
'tz',
'ua',
'ug',
'uk',
'university',
'uno',
'uol',
'us',
'uy',
'uz',
'V4',
'va',
'vanguard',
'vc',
've',
'vegas',
'ventures',
'vet',
'vg',
'vi',
'video',
'vig',
'villas',
'vip',
'vision',
'vlaanderen',
'vn',
'vodka',
'vote',
'vu',
'wales',
'watch',
'webcam',
'weber',
'website',
'weir',
'wf',
'whoswho',
'wien',
'wiki',
'wine',
'work',
'works',
'world',
'ws',
'wtf',
'xxx',
'xyz',
'yandex',
'ye',
'yoga',
'youtube',
'yu',
'yt',
'za',
'zm',
'zone',
'zuerich',
'zw',
'бел',
'онлайн',
'орг',
'рус',
'рф',
'срб',
'укр',
'հայ',
'قطر',
'中国',
'bugatti',
'an',
'fur',
'i2p',
'')
)
SELECT CONCAT( '# – ' , SUBSTRING_INDEX(SUBSTRING_INDEX(externallinks.el_to_domain_index,'.',1),'/',-1) 
              , '  {{Bearbeiten|',p.page_title ,'|text=',p.page_title,'}} – <nowiki> – '  
              ,  externallinks.el_to_domain_index ,' ––– ' 
              ,  externallinks.el_to_path ,' </nowiki> ' )as output 
FROM  externallinks INNER JOIN p
  ON p.page_id = externallinks.el_from
INNER JOIN TLDh 
  ON externallinks.el_to_domain_index = TLDh.el_to_domain_index
ORDER BY 1;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...