MySQL syntax error on FUNCTION creation

Question

I've been trying to find my error for hours but nothing. So here is my function:

DELIMITER //
DROP FUNCTION IF EXISTS func_name//
CREATE FUNCTION func_name(_num_client INT) RETURNS VARCHAR(20)
BEGIN
DECLARE n_titul VARCHAR(20) DEFAULT NULL;
SET n_titul = (SELECT UPPER(
    IF(
        length(concat_ws(' ', civilite, prenom, nom)),
        IF(
            length(concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom)),
            substring(concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom), 1, 20),
            concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom)
        ),
        concat_ws(' ', civilite, prenom, nom)
    )
) FROM client WHERE num_client=_num_client) ;
RETURN n_titul;
END;
//

And I get this response :

ERROR: Punctuation invalid @ 637 STR: // SQL: { HERE THE RESQUEST ABOVE }

#1064 - 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 17

Change your delimiter to $$ and remove // after DROP FUNCTION blah — Mihai
– Mihai, Commented Sep 26, 2013 at 15:04
Ok, so I replaced Delimiter // by Delimiter $$. Do I have to change all '//' by '$$'? (Cause you said remove, so it's wasn't very clear) — Roman Kos
– Roman Kos, Commented Sep 26, 2013 at 15:08

Mihai · Accepted Answer · 2013-09-26 15:13:53Z

1

DELIMITER //
DROP FUNCTION IF EXISTS func_name//
CREATE FUNCTION func_name(_num_client INT) RETURNS VARCHAR(20)
BEGIN
DECLARE n_titul VARCHAR(20) DEFAULT NULL;
SET n_titul = (SELECT UPPER(
    IF(
        length(concat_ws(' ', civilite, prenom, nom)),
        IF(
            length(concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom)),
            substring(concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom), 1, 20),
            concat_ws(' ', civilite, concat(substring(prenom, 1, 1), '.'), nom)
        ),
        concat_ws(' ', civilite, prenom, nom)
    )
) FROM client WHERE num_client=_num_client) ;
RETURN n_titul;
END//
DELIMITER ;

answered Sep 26, 2013 at 15:13

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

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

1 Comment

Roman Kos Over a year ago

Thnx, it worked with "DETERMINISTIC" added: CREATE FUNCTION func_name(_num_client INT) RETURNS VARCHAR(20) DETERMINISTIC Without it, it returned some error about "Deterministic, no sql, ... missing".

Collectives™ on Stack Overflow

MySQL syntax error on FUNCTION creation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related