MySQL Snippets

mysql-snippets.sql

/* match a string at the beginning of string */
SELECT 'Test' REGEXP '^The'; -- 0
SELECT 'The Test' REGEXP '^The'; -- 1

/* if a name is not prefixed with 'The ' then add it */
UPDATE [table]
SET Name = CONCAT('The ', TRIM(Name))
WHERE Name NOT REGEXP '^The'

/* copy a column from one table to another */
INSERT INTO [table] ([column]) SELECT [column] FROM [table]

/* remove all whitespace */
UPDATE [table] SET [column] = REPLACE([column], ' ', '')

/* (this is BASH) drop all tables in a database */
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

/* change to collation of a table and all of its existing columns */
alter table [table] convert to character set utf8 collate utf8_general_ci;