A Little Noise

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