Emotikony 2013.12.26

Próba 1

• Pobrano 100 wpisów z bazy danych dla meczu Manchester City - Arsenal FC (przefiltrowane tak, aby znalazły się tylko wpisy wprowadzone aplikacją webową - dzięki temu, pozbyto się wpisów generowanych przez boty i tym podobne; dodatkowo odrzucono wszystkie retweety)

SELECT text
FROM tweets
WHERE match_event = 715
	AND text NOT LIKE 'RT%'
	AND source = 'web'
order by created_at desc
limit 100

• Ręcznie znalezione wpisy z emotikonami (6)

"Gutted I missed the Man City Arsenal game :("
"#Arsenal Lost.. YAY
#Chelsea Won.. YAAAYYY :V"
"Where the hell am I? Missed the #CFC match as well as the CIty-Gunners nail-biter! :("
"@mithran_ns Yaya and Fernandinho will rip us apart. With lucas, hendo and allen it seems impossible hope rossiter can rise to the occasion;)"
"Manchester City 6 - Arsenal 3 :')"
"@AlAhly  & @Arsenal 
Clear this day from your memory .
:((("

• Znalezione użytym wcześniej zapytaniem (4)

SELECT * FROM (
	SELECT text
	FROM tweets
	WHERE match_event = 715
		AND text NOT LIKE 'RT%'
		AND source = 'web'
	order by created_at desc
	limit 100
) as view1
WHERE  (
text LIKE '%:)%' OR
text LIKE '%:D%' OR
text LIKE '%:(%' OR
text LIKE '%;)%' OR
text LIKE '%:-)%' OR
text LIKE '%:P%' OR
text LIKE '%=)%' OR
text LIKE '%(:%' OR
text LIKE '%;-)%' OR
(text LIKE '%:/%' AND text NOT LIKE '%://%') OR
text LIKE '%XD%' OR
text LIKE '%=D%' OR
text LIKE '%:o%' OR
text LIKE '%=]%' OR
-- text LIKE '%D:%' OR
text LIKE '%;D%' OR
text LIKE '%:]%' OR
text LIKE '%:-(%' OR
text LIKE '%=/%' OR
text LIKE '%=(%'
)

"Gutted I missed the Man City Arsenal game :("
"Where the hell am I? Missed the #CFC match as well as the CIty-Gunners nail-biter! :("
"@mithran_ns Yaya and Fernandinho will rip us apart. With lucas, hendo and allen it seems impossible hope rossiter can rise to the occasion;)"
"@AlAhly  & @Arsenal 
Clear this day from your memory .
:((("

• Nieznalezione (2)

"#Arsenal Lost.. YAY
#Chelsea Won.. YAAAYYY :V"
"Manchester City 6 - Arsenal 3 :')"

Próba 2

• tym razem pobrałem wszystkie ostatnie 100 wpisów z bazy bez jakiejkolwiek filtracji
• okazało się, że użyte zapytanie znalazło wszystkie wpisy z emotikonami
• znaleziono jedynie 2 takie

"@nr147 I totally agree he was awesum and never gave up wat a way 2 show liverpool wuz the boss ;)"
"RT @KSIOlajidebt: Well Liverpool fans, your team lost to chelsea again, but look on the bright side, now you know how good smith is :D"