MySql | custom function | split String into characters

Question

I am new to MySQL user functions, my requirement is:

a. take a varchar (name) as input,

b. split it into char by char

c. Loop for each char from name str: Do something.

DELIMITER //
    CREATE FUNCTION te1(name VARCHAR(64))
    RETURNS VARCHAR DETERMINISTIC
    BEGIN
    //Here I want to split name, and store it in some char[], so that I can iterate through it.
        //Iterate through characters
    
    END//
    DELIMITER ;

googled, but could not find a way to split a String into char[] in MySQL. In my case there is no delimiter, should split each char

Answer 1

Try this solution:

DELIMITER $$
CREATE FUNCTION te1(name VARCHAR(64)) RETURNS VARCHAR(64) DETERMINISTIC
BEGIN
    DECLARE max_length INT DEFAULT 0;
    DECLARE cur_pos INT DEFAULT 1;
    DECLARE test_result VARCHAR(64) DEFAULT '';

    SET max_length = CHAR_LENGTH(TRIM(name));
    myloop:LOOP
        -- Exit conditions
        IF max_length = 0 OR cur_pos > max_length OR name IS NULL THEN
            LEAVE myloop;
        END IF;


        -- Get your CHAR in every loop using SUBSTRING(TRIM(name),cur_pos,1) and do your stuff.
        -- This is a test variable used to show the function behavior. Its composed by every single char added during loops. If the result is the same as the input parameter, we can affirm that this function works well.
        SET test_result = CONCAT(test_result, SUBSTRING(TRIM(name),cur_pos,1));


        SET cur_pos = cur_pos + 1;
    END LOOP;
    RETURN test_result;
END$$

DELIMITER ;

-- Calling the function
SELECT te1('test');

Note: i'm using TRIM() in my example but obviously it's not mandatory.