nix, shell, perl, php, mysql and mac os x tips and tricks

Saturday, October 9, 2010

Neat MySQL SUBSTRING Functions

Chop off the first character of a string that matches a particular regex pattern:
SELECT SKUID, CATEGORY, SUBSTRING(CATEGORY,2) FROM products WHERE CATEGORY RLIKE '^;([0-9;]{1,20});$'

Chop off the last character of a string
SELECT SUBSTRING(CATEGORY,1,LENGTH(CATEGORY)-1) AS CATEGORY FROM products WHERE CATEGORY RLIKE '^([0-9;]{1,20});$';

Extract the domain name (including subdomain) from an URL:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url,'://',-1),'/',1) FROM urls

Monday, October 4, 2010

Recursively delete old directories

careful with this one!
/usr/bin/find /home/user/oldlogs/ -maxdepth 1 -type d -mtime +90 -exec rm -rf {} \;