Category: Technical

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

Explode a string around a delimiter without a loop.

DROP PROCEDURE IF EXISTS explode;

DELIMITER |

CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)                                
BEGIN                                
   DROP TABLE IF EXISTS temp_explode;                                
   CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));                                
   SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');                                
   PREPARE myStmt FROM @sql;                                
   EXECUTE myStmt;                                
END |   

DELIMITER ;

-- example call:
SET @str  = "The quick brown fox jumped over the lazy dog"; 
SET @delim = " "; 

CALL explode(@delim,@str);
SELECT id,word FROM temp_explode;

Extract the first, longest pattern that matches a regular expression.
You could add a parameter or write a similar function to get the shortest match instead (by starting with e = s and then incrementing e, instead of starting with e = LENGTH(string) and decrementing).

CREATE FUNCTION REGEXP_EXTRACT(string TEXT, exp TEXT)
-- Extract the first longest string that matches the regular expression
-- If the string is 'ABCD', check all strings and see what matches: 'ABCD', 'ABC', 'AB', 'A', 'BCD', 'BC', 'B', 'CD', 'C', 'D'
-- It's not smart enough to handle things like (A)|(BCD) correctly in that it will return the whole string, not just the matching token.

RETURNS TEXT
DETERMINISTIC
BEGIN
  DECLARE s INT DEFAULT 1;
  DECLARE e INT;
  DECLARE adjustStart TINYINT DEFAULT 1;
  DECLARE adjustEnd TINYINT DEFAULT 1;

  -- Because REGEXP matches anywhere in the string, and we only want the part that matches, adjust the expression to add '^' and '$'
  -- Of course, if those are already there, don't add them, but change the method of extraction accordingly.

  IF LEFT(exp, 1) = '^' THEN 
    SET adjustStart = 0;
  ELSE
    SET exp = CONCAT('^', exp);
  END IF;

  IF RIGHT(exp, 1) = '$' THEN
    SET adjustEnd = 0;
  ELSE
    SET exp = CONCAT(exp, '$');
  END IF;

  -- Loop through the string, moving the end pointer back towards the start pointer, then advance the start pointer and repeat
  -- Bail out of the loops early if the original expression started with '^' or ended with '$', since that means the pointers can't move
  WHILE (s <= LENGTH(string)) DO
    SET e = LENGTH(string);
    WHILE (e >= 1) DO
      IF SUBSTRING(string, s, e) REGEXP exp THEN
        RETURN SUBSTRING(string, s, e);
      END IF;
      IF adjustEnd THEN
        SET e = e - 1;
      ELSE
        SET e = s - 1; -- ugh, such a hack to end it early
      END IF;
    END WHILE;
    IF adjustStart THEN
      SET s = s + 1;
    ELSE
      SET s = LENGTH(string) + 1; -- ugh, such a hack to end it early
    END IF;
  END WHILE;

  RETURN NULL;

END