Update String in column with caps first letter every word in MYSQL

Tujuan function: Untuk update string dalam kolumn yang all caps seperti ABCD EFGH IJK-L kepada Abcd Efgh Ijk-l gunakan function ciptaan CAP_FIRST atau All Caps kepada Abcd efgh ijk-l guna.

Objective: To update string in column with all caps like ABCD EFGH IJK-L to Abcd Efgh Ijk-l with a custom function CAP_FISRT or using ucfirst to capitalize first letter of the sentences.

DROP FUNCTION IF EXISTS ucfirst;
DROP FUNCTION IF EXISTS CAP_FIRST;

DELIMITER $$
CREATE FUNCTION ucfirst(str_value VARCHAR(5000))
RETURNS VARCHAR(5000)
DETERMINISTIC
BEGIN
RETURN CONCAT(UCASE(LEFT(str_value, 1)),SUBSTRING(str_value, 2));
END
$$

DELIMITER $$

CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255) CHARSET UTF8MB4)

RETURNS VARCHAR(255) CHARSET UTF8MB4

DETERMINISTIC

BEGIN
DECLARE len INT;
DECLARE i INT;

SET len   = CHAR_LENGTH(INPUT);
SET INPUT = LOWER(INPUT);
SET i = 0;

WHILE (i < len) DO
    IF (MID(INPUT,i,1) = ' ' OR i = 0) THEN
        IF (i < len) THEN
            SET INPUT = CONCAT(
                LEFT(INPUT,i),
                UPPER(MID(INPUT,i + 1,1)),
                RIGHT(INPUT,len - i - 1)
            );
        END IF;
    END IF;
    SET i = i + 1;
END WHILE;

RETURN INPUT;

END$$
DELIMITER ;

SELECT
post_title,
CAP_FIRST(lower(post_title)) AS output
FROM wp_posts_c WHERE post_type = ‘product’