asked on

MySQL Create Function

Found this function that I'd like to use but it gives me an error at the declare len int; line. I'm running MySQL 5.0. Below is the code.

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;

Open in new window

theGhost_k8

Hi,
Function looked fine to me!!!!
I tried to build it step by step and it worked....
check the attached code !

CREATE DEFINER=`root`@`localhost` FUNCTION `CAP_FIRST`(input varchar(255)) RETURNS varchar(255) CHARSET latin1
    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

Open in new window

azyet24

ASKER

I get this message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT' at line 5

I am using MySQL query browser

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theGhost_k8

Ignore db1 in above code!

azyet24

ASKER

Worked perfectly. Thanks for the step by step details. This is my first experience with function so I was clueless.

azyet24

ASKER

I hope you might can answer something for me. The cap_first function that you helped me to created has now disappeared when we upgraded mysql from 5.1 to 5.4. When I repeate the steps it will not add the function. The error message says:
Script line: 4 Failed to CREATE FUNCTION CAP_FIRST

I have recreated it on 5.1 and tried to backup/restore into 5.4 but that fails as well. Do you know what needs to be changed in the create statement to get this to work in 5.4?

azyet24

ASKER

Nevermind, there was an error in the DB that caused the problem.