In response to http://forge.mysql.com/snippets/view.php?id=60 – adding support to count strings, not just characters
( character_length(<string1>) - character_length(REPLACE(<string1>, <searchString>, '') ) / character_length(<searchString>)
In response to http://forge.mysql.com/snippets/view.php?id=60 – adding support to count strings, not just characters
( character_length(<string1>) - character_length(REPLACE(<string1>, <searchString>, '') ) / character_length(<searchString>)
Finds the date for Easter (Western) given a year.
Sample usage: SELECT easter(YEAR(NOW()));
DELIMITER // CREATE FUNCTION easter(inYear YEAR) RETURNS DATE DETERMINISTIC BEGIN DECLARE a, b, c, d, e, k, m, n, p, q INT; DECLARE easter DATE; SET k = FLOOR(inYear / 100); SET a = MOD(inYear, 19); SET b = MOD(inYear, 4); SET c = MOD(inYear, 7); SET q = FLOOR(k / 4); SET p = FLOOR((13 + 8 * k) / 25); SET m = MOD((15-p+k-q), 30); SET d = MOD((19 * a + m), 30); SET n = MOD((4+k-q), 7); SET e = MOD((2*b+4*c+6*d+n), 7); SET easter = CASE WHEN d + e <= 9 THEN CONCAT_WS('-', inYear, '03', 22 + d + e) WHEN d = 29 AND e = 6 THEN CONCAT_WS('-', inYear, '04-19') WHEN d = 28 AND e = 6 AND a > 10 THEN CONCAT_WS('-', inYear, '04-18') ELSE CONCAT_WS('-', inYear, '04', LPAD(d + e - 9, 2, 0)) END; RETURN easter; END // DELIMITER ;
A stored procedure to show all grants in the database.
USE mysql; DELIMITER // CREATE PROCEDURE showAllGrants() BEGIN DECLARE done INT DEFAULT 0; DECLARE theUser CHAR(16); DECLARE theHost CHAR(60); DECLARE cur1 CURSOR FOR SELECT user, host FROM mysql.user; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO theUser, theHost; IF NOT done THEN SET @sql := CONCAT('SHOW GRANTS FOR ', QUOTE(theUser), '@', QUOTE(theHost)); PREPARE grantStatement FROM @sql; EXECUTE grantStatement; DROP PREPARE grantStatement; END IF; UNTIL done END REPEAT; CLOSE cur1; END// DELIMITER ; CALL showAllGrants();
Convert an integer in the range 0 to 3999 to Roman numerals.
CREATE FUNCTION `toRoman`(inArabic int unsigned) RETURNS varchar(15) CHARSET latin1 DETERMINISTIC BEGIN DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM'; DECLARE stringInUse CHAR(3); DECLARE position tinyint DEFAULT 1; DECLARE currentDigit tinyint; DECLARE returnValue VARCHAR(15) DEFAULT ''; IF(inArabic > 3999) THEN RETURN 'overflow'; END IF; IF(inArabic = 0) THEN RETURN 'N'; END IF; WHILE position <= CEIL(LOG10(inArabic + .1)) DO SET currentDigit := MOD(FLOOR(inArabic / POW(10, position - 1)), 10); SET returnValue := CONCAT( CASE currentDigit WHEN 4 THEN CONCAT(SUBSTRING(numeral, position * 2 - 1, 1), SUBSTRING(numeral, position * 2, 1)) WHEN 9 THEN CONCAT(SUBSTRING(numeral, position * 2 - 1, 1), SUBSTRING(numeral, position * 2 + 1, 1)) ELSE CONCAT( REPEAT(SUBSTRING(numeral, position * 2, 1), currentDigit >= 5), REPEAT(SUBSTRING(numeral, position * 2 - 1, 1), MOD(currentDigit, 5)) ) END, returnValue); SET position := position + 1; END WHILE; RETURN returnValue; END
SHOW CREATE TRIGGER for 5.0 – 5.1.20
CREATE FUNCTION SHOW_CREATE_TRIGGER(in_trigger varchar(255)) RETURNS text READS SQL DATA BEGIN DECLARE returnText text; SELECT CONCAT_WS( " ", "CREATE TRIGGER", TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, "ON", EVENT_OBJECT_TABLE, "FOR EACH ROW", ACTION_STATEMENT) into returnText FROM information_schema.triggers; RETURN returnText; END
Translate from Roman Numerals back to decimal. Legal range is 0 to 3999 (that is, N to MMMCMXCIX) You can use IV or IIII for 4. You could even use a string of twenty I’s for 20, but there’s a string limit of 15, since that’s the length of the biggest well-formed numeral below 3999.
CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC BEGIN DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM'; DECLARE digit TINYINT; DECLARE previous INT DEFAULT 0; DECLARE current INT; DECLARE sum INT DEFAULT 0; SET inRoman = UPPER(inRoman); WHILE LENGTH(inRoman) > 0 DO SET digit := LOCATE(RIGHT(inRoman, 1), numeral) - 1; SET current := POW(10, FLOOR(digit / 2)) * POW(5, MOD(digit, 2)); SET sum := sum + POW(-1, current < previous) * current; SET previous := current; SET inRoman = LEFT(inRoman, LENGTH(inRoman) - 1); END WHILE; RETURN sum; END
Given a source string and a two-character range string, extracts all characters from the source that fall within the range. Sort of a simple version of the [A-Z] syntax from regular expressions.
Examples:
extractRange(‘123-ABC-456’, ‘AZ’) returns ‘ABC’
extractRange(‘123-ABC-456′, ’09’) returns ‘123456’
CREATE FUNCTION extractRange(inString TEXT, inRange char(2)) RETURNS TEXT DETERMINISTIC BEGIN DECLARE returnString TEXT DEFAULT ''; DECLARE pointer INT DEFAULT 1; WHILE pointer <= CHAR_LENGTH(inString) DO IF MID(inString, pointer, 1) BETWEEN LEFT(inRange, 1) AND RIGHT(inRange, 1) THEN SET returnString := CONCAT(returnString, MID(inString, pointer, 1)); END IF; SET pointer := pointer + 1; END WHILE; return returnString; END
Two functions that work just like FIND_IN_SET, but support the % wildcard.
SELECT FIND_WILD_IN_SET(‘%tack’, ‘haystack,bale,heap,needle,pile’);
SELECT FIND_IN_WILD_SET(‘pin’, ‘haystack,bale,heap,needle,pi%’);
DROP FUNCTION IF EXISTS FIND_IN_WILD_SET; DROP FUNCTION IF EXISTS FIND_WILD_IN_SET; DELIMITER // CREATE FUNCTION FIND_IN_WILD_SET(theString text, theSet text) RETURNS boolean DETERMINISTIC BEGIN DECLARE delimiterCount int; DECLARE pos int DEFAULT 0; DECLARE setElement text; DECLARE returnValue boolean DEFAULT FALSE; SET delimiterCount := CHARACTER_LENGTH(theSet) - CHARACTER_LENGTH(REPLACE(theSet, ',', '')); WHILE (pos <= delimiterCount) DO BEGIN SET setElement := SUBSTRING_INDEX(SUBSTRING_INDEX(theSet, ',', pos+1), ',', -1); SET returnValue := returnValue OR (theString LIKE setElement); SET pos := pos + 1; END; END WHILE; RETURN returnValue; END// CREATE FUNCTION FIND_WILD_IN_SET(theString text, theSet text) RETURNS boolean DETERMINISTIC BEGIN DECLARE delimiterCount int; DECLARE pos int DEFAULT 0; DECLARE setElement text; DECLARE returnValue boolean DEFAULT FALSE; SET delimiterCount := CHARACTER_LENGTH(theSet) - CHARACTER_LENGTH(REPLACE(theSet, ',', '')); WHILE (pos <= delimiterCount) DO BEGIN SET setElement := SUBSTRING_INDEX(SUBSTRING_INDEX(theSet, ',', pos+1), ',', -1); SET returnValue := returnValue OR (setElement LIKE theString); SET pos := pos + 1; END; END WHILE; RETURN returnValue; END// DELIMITER ;
A stored function to guess which line terminator (‘\r’ or ‘\r\n’) to use with LOAD DATA INFILE
CREATE FUNCTION whichLineTerminator(fileName varchar(255)) RETURNS varchar(20) NOT DETERMINISTIC BEGIN DECLARE cr_count int; DECLARE lf_Count int; DECLARE f text; SET f := LOAD_FILE(fileName); IF f IS NULL THEN RETURN 'Cannot read file.'; END IF; SET cr_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\r', '')); SET lf_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\n', '')); IF cr_count >= lf_count THEN RETURN '\\r\\n'; ELSE RETURN '\\n'; END IF; END
Find the greatest common factor of two positive integers.
CREATE FUNCTION gcd(x int, y int) RETURNS int DETERMINISTIC BEGIN DECLARE dividend int; DECLARE divisor int; DECLARE remainder int; SET dividend := GREATEST(x, y); SET remainder := LEAST(x, y); WHILE remainder != 0 DO SET divisor = remainder; SET remainder = MOD(dividend, divisor); SET dividend = divisor; END WHILE; RETURN divisor; END
Powered by WordPress