Strip leading zeroes using regex

Doesn't actually "strip", rather captures the non-laeding-zero number. Works only on a string that only contains numbers, and is compatible with ancient regex versions.

^[0]+([1-9][0-9]{1,})$

NOTE: will NOT match a number that doesn't have leading zeroes

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

Recursively delete old directories

careful with this one!

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

Use 'find' to recursively chmod files

another 'find' quickie...

find /home/user/files/ -name 'myfile.sh' -exec chmod 755 {} \;

Capitalize the first letter of a sentence with MYSQL

UPDATE `categories` SET category_name = CONCAT(UCASE(SUBSTRING(`category_name`, 1,1)),LOWER(SUBSTRING(`category_name`, 2)))

MySQL function for replacing HTML entities with their actual characters

For a more comprehensive function containing more special characters see http://forums.mysql.com/read.php?98,246527

Actually, better use that link (above). Blogger is messing with the code below.

Usage:

SELECT HTML_UnEncode(firstname),HTML_UnEncode(lastname) FROM users;

DELIMITER $$

DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode`$$
CREATE DEFINER=`user`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE TextString VARCHAR(255) ;
SET TextString = x ;

#quotation mark
IF INSTR( x , '"' )
THEN SET TextString = REPLACE(TextString, '"','"') ;
END IF ;

#apostrophe 
IF INSTR( x , ''' )
THEN SET TextString = REPLACE(TextString, ''','"') ;
END IF ;

#ampersand
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&','&') ;
END IF ;

#less-than
IF INSTR( x , '<' )
THEN SET TextString = REPLACE(TextString, '<','<') ;
END IF ;

#greater-than
IF INSTR( x , '>' )
THEN SET TextString = REPLACE(TextString, '>','>') ;
END IF ;

#non-breaking space
IF INSTR( x , ' ' )
THEN SET TextString = REPLACE(TextString, ' ',' ') ;
END IF ;

RETURN TextString ;

END$$

DELIMITER ;

Find duplicate records in MySQL

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

sed one-liner for removing HTML tags from a file

sed -e 's/<[^>]*>//g' myfile.html

Regular expression to select the first 50 chars of a string, respecting words

^(.{50,50}[^ ]*)

Create report on top domains represented in a table of e-mail addresses

Shows the count for each domain, and the percentage of the total each domain represents. For opt-in e-mail addresses only. Excludes any domain that has less than 100 associated e-mails (therefore showing only the major ones).

SELECT 
 COUNT(c.email) as count, 
 CONCAT(FORMAT((COUNT(c.email)/t.TOTAL)*100, 2),"%") AS percent,
 SUBSTRING_INDEX(c.email,'@',-1) AS domain 
FROM email_table AS c,  
 (SELECT count(email) as TOTAL 
 FROM email_table 
 WHERE optin="yes")
 AS t
WHERE optin = "yes" 
GROUP BY domain 
HAVING COUNT > 100
ORDER BY COUNT DESC

Select the id of a record that is 6 months old in MySQL

When you need the id of a single record that is 6 months old, and there's a timestamp or date column. Useful if the id is auto-incremented primary key and you need to do a DELETE query in IDs less than returned id (which would be MUCH faster than doing it by a non-indexed timestamp field).

SELECT MAX(id) FROM mail_links WHERE timestamp BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 6 MONTH), INTERVAL 1 DAY) AND DATE_SUB(NOW(), INTERVAL 6 MONTH);