Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

What is the correct syntax for a set command within a mySQL user defined function?

0

44 views

I'm receiving the following error when trying to create a function in mySQL 5.1:

Error Code : 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 25 (0 ms taken)

Here is my code:

DELIMITER $$  

CREATE
    FUNCTION removeMethodAndBackslash(input VARCHAR(40))
    RETURNS VARCHAR(20)
    BEGIN

    DECLARE loginName VARCHAR(40);
    SET loginName = input;

    IF SUBSTRING(loginName,7) LIKE '\b%' THEN
        SET loginName = 'b' + SUBSTRING(input,8);

    ELSE IF SUBSTRING(loginName,7) LIKE '\n%' THEN
        SET loginName = 'n' + SUBSTRING(input,8);

    ELSE IF SUBSTRING(loginName,7) LIKE '\t%' THEN
        SET loginName = 't' + SUBSTRING(input,8);

    ELSE IF SUBSTRING(loginName,7) LIKE '\r%' THEN
        SET loginName = 'r' + SUBSTRING(input,8);

    ELSE
        SET loginName = SUBSTRING(input,7);

    RETURN loginName;

END $$

DELIMITER ;

I've been playing with the syntax for a while and I'm certain its a very small detail that I'm overlooking. Any help would be greatly appreciated.

asked April 5, 2011 8:35 am CDT
posted via StackOverflow

2 Answers

1
 

you can use CASE...THEN...ELSE block

DELIMITER $$  

CREATE FUNCTION removeMethodAndBackslash(input VARCHAR(40))
RETURNS VARCHAR(20)
    BEGIN

    DECLARE loginName VARCHAR(40);

    SET loginName = 
       CASE WHEN SUBSTRING(input,7) LIKE '\b%' THEN 'b' + SUBSTRING(input,8)
            WHEN SUBSTRING(input,7) LIKE '\n%' THEN 'n' + SUBSTRING(input,8)
            WHEN SUBSTRING(input,7) LIKE '\t%' THEN 't' + SUBSTRING(input,8)
            WHEN SUBSTRING(input,7) LIKE '\r%' THEN 'r' + SUBSTRING(input,8)
            ELSE SUBSTRING(input,7)
       END;

    RETURN loginName;

END $$

answered April 5, 2011 9:23 am CDT
1
Best answer
 

Try this one -

DELIMITER $$  

CREATE
    FUNCTION removeMethodAndBackslash(input VARCHAR(40))
    RETURNS VARCHAR(20)
    BEGIN

    DECLARE loginName VARCHAR(40);
    SET loginName = input;

    IF SUBSTRING(loginName,7) LIKE '\b%' THEN
        SET loginName = 'b' + SUBSTRING(input,8);

    ELSEIF SUBSTRING(loginName,7) LIKE '\n%' THEN
        SET loginName = 'n' + SUBSTRING(input,8);

    ELSEIF SUBSTRING(loginName,7) LIKE '\t%' THEN
        SET loginName = 't' + SUBSTRING(input,8);

    ELSEIF SUBSTRING(loginName,7) LIKE '\r%' THEN
        SET loginName = 'r' + SUBSTRING(input,8);

    ELSE
        SET loginName = SUBSTRING(input,7);
    END IF;

    RETURN loginName;

END $$

DELIMITER ;

answered April 5, 2011 9:23 am CDT

Your answer

Join with account you already have


Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions