MySQL - Storing variables inside CREATE FUNCTION

Question

I'm trying to figure out how to store variables inside a function in mySQL. I am trying to create a function that capitalizes a field name. Creating a function works if I don't create variables. The problem is this is difficult to read, and is easy to make mistakes with.

CREATE FUNCTION capitalize(string TEXT)
    RETURNS TEXT
    RETURN CONCAT(UPPER(LEFT(string,1)), LOWER(RIGHT(string, LENGTH(string) - 1)));

When I try to add variables using the DECLARE and SET keywords, it no longer works.

CREATE FUNCTION capitalize(string TEXT)
    RETURNS TEXT
    DECLARE first_letter TEXT;
    DECLARE last_letters TEXT;
    SET first_letter = UPPER(LEFT(string,1));
    SET last_letters = LOWER(RIGHT(string, LENGTH(string) - 1));
    RETURN CONCAT(first_letter, last_letters);

I get this error 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 '' at line 4

I've fiddled around with it, removing semicolons, and double/triple checking parentheses. I've fiddled with BEGIN and END statements but nothing seems to work at all.

I have searched extensively on this topic but cannot figure out where the problem lies.

are multiple declares allowed at all? You can declare multiple vars with a single `declare`... — Marc B, Jan 04 '16 at 17:39
What MySQL client are you using to create this? You probably need to first add the `BEGIN/END` and then [specify an alternate delimiter.](http://stackoverflow.com/questions/10259504/delimiters-in-mysql) Doing both of those things, I was able to create your function. — Michael Berkowski, Jan 04 '16 at 17:40
Cmment everything out. Add a command to return the string. If that runs successfully, uncomment the commands one by one until the error reappears. — Dan Bracuk, Jan 04 '16 at 17:40
@MichaelBerkowski No you don't. Here's an SQLFiddle of the working example. http://sqlfiddle.com/#!9/b1e9e — Richard Hamilton, Jan 04 '16 at 17:44
@RichardHamilton Yes you do. That version of the function only executes _one_ SQL statement. The variable declarations make it into a multi-statement body, which means you need a `BEGIN/END`. — Michael Berkowski, Jan 04 '16 at 17:44

Darwin von Corax · Accepted Answer · 2016-01-04T17:52:46.757

1

The body of a CREATE FUNCTION can consist of only a single statement, which is why the first version works and the second doesn't. Fortunately, that single statement can be a compound statement enclosed in a BEGIN ... END block.

You need to enclose the function body in a BEGIN ... END block to allow MySQL to see it as a single statement; you'll also perhaps need to precede and follow it with DELIMITER statements (depending on your client, as Mr. Berkowski points out):

DELIMITER //

CREATE FUNCTION capitalize(string TEXT)
    RETURNS TEXT
    BEGIN
      DECLARE first_letter TEXT;
      DECLARE last_letters TEXT;
      SET first_letter = UPPER(LEFT(string,1));
      SET last_letters = LOWER(RIGHT(string, LENGTH(string) - 1));
      RETURN CONCAT(first_letter, last_letters);
    END; //

DELIMITER ;

(Note especially the space between the last DELIMITER and the semicolon.)

edited Jan 04 '16 at 17:52

answered Jan 04 '16 at 17:45

Darwin von Corax

5,201
3
17
28

Note that the mechanism for specifying the delimiter is client-specific. The official MySQL CLI client and Workbench use the `DELIMITER` keyword, but other clients have different implementations (input fields, configuration options, etc) – Michael Berkowski Jan 04 '16 at 17:48
I'll have to take your word for that; I'm only familiar with the two official clients. – Darwin von Corax Jan 04 '16 at 17:49
Are you sure about this? I entered it into sqlfiddle and got the following. 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 'DELIMITER // – Richard Hamilton Jan 04 '16 at 17:50
@RichardHamilton See my comment. The `DELIMITER` part is needed for the official MySQL clients (maybe others) but sqlfiddle does not need it. It's not part of the language, but rather a client option. Others like PHPMyAdmin for example have an input field to handle it. What client are you using to create the function? – Michael Berkowski Jan 04 '16 at 17:52
DOH! You're right, Richard; see my latest edit. Aside from that, I just tested it in mysql (cli) and got no error. – Darwin von Corax Jan 04 '16 at 17:53
Still can't get it to work. But the logic does seem right. Should I just chalk this up to a problem with sqlfiddle and assume it works otherwise? – Richard Hamilton Jan 04 '16 at 17:59
Try it in SQLFiddle *without* the `DELIMITER`s, then perhaps try it both ways on your own system. – Darwin von Corax Jan 04 '16 at 18:01

MySQL - Storing variables inside CREATE FUNCTION

1 Answers1