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
– Marc B, Commented Jan 4, 2016 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. Doing both of those things, I was able to create your function. — Michael Berkowski
– Michael Berkowski, Commented Jan 4, 2016 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
– Dan Bracuk, Commented Jan 4, 2016 at 17:40
@MichaelBerkowski No you don't. Here's an SQLFiddle of the working example. sqlfiddle.com/#!9/b1e9e — Richard Hamilton
– Richard Hamilton, Commented Jan 4, 2016 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
– Michael Berkowski, Commented Jan 4, 2016 at 17:44

Darwin von Corax · Accepted Answer · 2016-01-04 17:52:46Z

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 4, 2016 at 17:52

answered Jan 4, 2016 at 17:45

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Berkowski Over a year ago

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)

Darwin von Corax Over a year ago

I'll have to take your word for that; I'm only familiar with the two official clients.

Richard Hamilton Over a year ago

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 //

Michael Berkowski Over a year ago

@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?

Darwin von Corax Over a year ago

DOH! You're right, Richard; see my latest edit. Aside from that, I just tested it in mysql (cli) and got no error.

|

Collectives™ on Stack Overflow

MySQL - Storing variables inside CREATE FUNCTION

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related