SUBSTR() som resultat

Hej!
Jag har en tabell med en massa värden (URL-adresser) som jag vill gruppera per domän - alltså oberoende av vilken websida som är lagrad i adressen. Kan man på något sätt göra en sådan jämförelse?

Med andra ord:
Tabellstruktur

m_url
-----
http://www.domän.se/sida1.html
http://www.domän.se/sida2.html
http://www.domain.com/page1.html
http://www.domain.com/page2.html
http://www.domain.com/page3.html

Förväntat resultat:

www.domän.se - 2
www.domain.com - 3

DBMS är MySQL.

Med vänliga,

Ungefär

select url,count(*)
  from
     (select substring(m_url from 8 for position('/' in substring(m_url from 8))) as url
        from t) dt
  group by url

Det blev perfekt. Thanks. :)

Nu har jag satt mig själv i klistret tror jag. ;)

Från ovanstående eminenta lösning, får jag ibland svar som:

by129.bay122.mail.live.com - 1
by191.bay166.mail.live.com - 1
f012.mail.spray.se - 1
www.microsoft.com - 10
...

Kan man på något sätt även få ut enbart toppdomänen? Jag har försökt med REPLACE() samt REGEXP(), men inser att jag inte alls är bra på det här med strängmanipulering i SQL. ;)

Förväntat resultat borde alltså vara:

mail.live.com - 2
mail.spray.se - 1
microsoft.com - 10
...

Alternativt:

live.com - 2
spray.se - 1
microsoft.com - 10
...

Finns det någon snygg lösning på det, möjligtvis?

Med vänliga,

Jag förutsätter att om substringindex inte hittar angivet antal delimitrar så returneras hela strängen. Det står inte dokumenterat hur det fungerar i dessa fall.

select substring_index(substring(url from 8  
   for position('/' in substring(url from 8)))-1,
  '.',-(char_length(url)-char_length(replace(url,'.',''))-2)) from spin

Snygg?

Jag får inte ut något vettigt tyvärr. Det verkar som att substring_index() returnerar en tom sträng när matchningen inte träffar. Men jag uppskattar försöket. :)

Hur det skulle se ut i SQL vågar jag mig inte på att skriva, men som en tanke... Kan du inte först plocka ut länkarna enligt den första lösningen och sedan i ett andra steg på något sätt använda RIGHT och placeringen av 2:a eller 3:e '.':n bakifrån?

Jo, jag har försökt det också - men problemet ligger just i "2:a eller 3:e '.':n". Eftersom att jag inte vet hur många punkter/subdomäner som existerar i adressen har jag stora problem att göra på det sättet. Just nu finns det mellan 1 och 4 punkter i en adress, som är strippad enligt första lösningen. :) :l

Humm....

SPiN skrev:

Alternativt:

live.com - 2
spray.se - 1
microsoft.com - 10
...

Här finns ju bara en punkt kvar i det du vill ha ut. Om det inte är en tillfällighet kan du ju köra på 2:a '.':n. Eller? :q

Förresten...

SPiN skrev:

Jag får inte ut något vettigt tyvärr. Det verkar som att substring_index() returnerar en tom sträng när matchningen inte träffar. Men jag uppskattar försöket. :)

Varför kollar du helt enkelt inte om strängen du får ut är tom och i så fall tar ut den med förra lösningen istället?

Du kan kontrollera jur många punkter det finns i strängen och välja lämplig metod

case when char_length(url)-char_length(replace(url,'.'.'')) > 2 then  
-- lösning för tre eller fler punkter         
when char_length(url)-char_length(replace(url,'.'.'')) = 2 then 
-- lösning för två punkter
else
-- lösning för 0 eller 1 punkt
end

SPiN skrev:

Nu har jag satt mig själv i klistret tror jag. ;)

Från ovanstående eminenta lösning, får jag ibland svar som:

by129.bay122.mail.live.com - 1
by191.bay166.mail.live.com - 1
f012.mail.spray.se - 1
www.microsoft.com - 10
...

Japp, klister galore. För du kommer väl ihåg att vissa toppdomäner alltid har två punkter, t.ex. brittiska domäner (.org.uk, .co.uk, .gov.uk, etc)

Skam den som ger sig!

Hittade en lösning tillslut - på mysql.com tror jag - som jag skrev om lite. Resultatet:

SELECT
(SUBSTRING_INDEX(
	SUBSTRING_INDEX(
		SUBSTRING_INDEX(
			TRIM(LEADING 'http://' FROM TRIM(LEADING 'https://' FROM myUrl)),
		'/', 1),
	':', 1), '.',
IF(
	myURL LIKE '%.org.__%' OR
	myURL LIKE '%.net.__%' OR
	myURL LIKE '%.com.__%' OR
	myURL LIKE '%.gov.__%' OR
	myURL LIKE '%.co.__%' OR
	myURL LIKE '%.__.us%' OR
	myURL LIKE '%.__.uk%',
-3, -2)))
	AS topdomain
FROM
	myTable

Fungerar ypperligt. Tack för hjälpen! :)

Kul att det löste sig! :)

I det här fallet är det väl verkligen läge att markera eget svar som det som löste biffen...