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

No comments:

Post a Comment