Change lower case to upper (title) case using sql query

Question

i want to change case using sql query

e.g if text is : My nAme is iShAn halaRNkar (text is jumbled i.e it may contain Lower case or Upper case anywhere in the senetence)

than i want the output to be : My Name Is Ishan Halarnkar

i have not worked on sql queries much. Kindly help.

Answer 1

There's no such function in any database which do this for you. You've to write a function which actually performs the check on each word in a sentence. Please check the solutions below:

MySql:

DELIMITER //

    CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

    RETURNS VARCHAR(255)

    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 ;

Example:

SELECT CAP_FIRST('this is exACtly tHe same!')

Output:

This Is Exactly The Same!

Copyrights:

http://joezack.com/2008/10/20/mysql-capitalize-function/

Hope this helps!