Saturday, August 19, 2006

Awking Mysql

How do you perform awk type functions in Mysql?

Say you have a list of urls and you want to find the most common domain names hosting gif files.

If this list is in a file you can use standard linux commands to find the most common domains.

grep '\.mp3' my_pictures |awk -F'/' '{print $3}' |sort |uniq -c|sort -nr

If the list is in a mysql database you can use locate() and substring() to accomplish the same thing!

SELECT count(*) as count, substring(url,1,locate('/',url,9)) as f FROM `my_pictures` WHERE url IS NOT NULL and url !='' and filename like '%.mp3' group by f order by count desc

Happy Awking!
Post a Comment