Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Sep 2013
    Location
    In front of a computer
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Invisible Syntax Error

    I'm trying to make some stored functions for a database, and I began by entering the simplest one, designed to prepare strings for a LIKE statement. Here's the code, after setting the delimiter to //:

    Code:
    mysql> CREATE FUNCTION PrepString (the_string VARCHAR(511), max_len TINYINT)
        -> RETURNS VARCHAR(511)
        -> BEGIN
        -> IF the_string IS NULL OR LENGTH(the_string) = 0 THEN SET the_string = '%';
        -> ELSE IF LENGTH(the_string) != max_len THEN SET the_string = CONCAT('%', the_string, '%');
        -> END IF;
        -> RETURN the_string;
        -> END//
    Here's what I get:

    Code:
    ERROR 1064 (42000): 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 8
    So, apparently there's a problem with the END statement, but it won't say where. I've tried this both with and without a semicolon after the END, getting the same result both times. What am I doing wrong?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    You are misusing ELSE IF.

    Or, rather, you are probably used to the syntax in VB and other similar languages.

    MySQL does *NOT* have a special syntax for ELSE IF. The IF is completely separate and each IF needs its own matching END IF.

    Try this:
    Code:
    delimiter //
    
    CREATE FUNCTION PrepString (the_string VARCHAR(511), max_len TINYINT)
    RETURNS VARCHAR(511)
    BEGIN
       IF the_string IS NULL OR LENGTH(the_string) = 0 THEN 
           SET the_string = '%';
       ELSE 
           IF LENGTH(the_string) != max_len THEN 
               SET the_string = CONCAT('%', the_string, '%');
           END IF;
       END IF;
       RETURN the_string;
    END//
    
    delimiter ;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    NotPanicking (09-06-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    As a slight modification to that may I suggest:
    Code:
    delimiter //
    
    CREATE FUNCTION PrepString (the_string VARCHAR(511), max_len TINYINT)
    RETURNS VARCHAR(511)
    BEGIN
       IF IFNULL(the_string,'') = '' THEN 
           SET the_string = '%';
       ELSE 
           IF LENGTH(the_string) != max_len THEN 
               SET the_string = CONCAT('%', the_string, '%');
           END IF;
       END IF;
       RETURN the_string;
    END//
    
    delimiter ;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    And shouldn't this line
    Code:
        IF LENGTH(the_string) != max_len THEN
    really be
    Code:
        IF LENGTH(the_string) < max_len THEN
    As it is, if the_string is *LONGER* than max_len, you would still do the CONCAT.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Sep 2013
    Location
    In front of a computer
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Oh. Huh. Well. That is tremendously helpful. Thank you!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •