• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • Last Modified:

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

0
azyet24
Asked:
azyet24
  • 4
  • 3
1 Solution
 
K VDatabase ConsultantCommented:
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

0
 
azyet24Author Commented:
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
0
 
K VDatabase ConsultantCommented:

My Steps:

1>

Open Query browser, open new script for function xyz.
Rename xyz and make it CAP_FIRST.
Add return statement and deterministic @ places.
Execute.
----------------- this is how it looked -------------
DELIMITER $$

DROP FUNCTION IF EXISTS `db1`.`cap_first` $$
CREATE FUNCTION `db1`.`cap_first` () RETURNS INT
deterministic
BEGIN
return 1;
END $$

DELIMITER ;
-----------------------------------------

2> Alter return types and input variables and return statement with return "" to verify.
Execute. It will work again.

----------------- this is how it looked -------------
DELIMITER $$

DROP FUNCTION IF EXISTS `db1`.`cap_first` $$
CREATE FUNCTION `db1`.`cap_first` (input varchar(255)) RETURNS varchar(255) CHARSET latin1
deterministic
BEGIN
return "";
END $$

DELIMITER ;
------------------------------------------

3> copy paste your code from line4 to line 24
And function gets ready without error !!

Code attached above!
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
K VDatabase ConsultantCommented:
Ignore db1 in above code!
0
 
azyet24Author Commented:
Worked perfectly.  Thanks for the step by step details.  This is my first experience with function so I was clueless.
0
 
azyet24Author Commented:
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?
0
 
azyet24Author Commented:
Nevermind, there was an error in the DB that caused the problem.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now