Results 1 to 2 of 2

Thread: MySQL function

  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: MySQL function

    Hi guys, I having problem converting the sql server function to mysql. It work well at the sql server but display out the error not allowed to return a result set from a function. Please help thanks.

    Code:
    delimiter $$ 
    
    CREATE FUNCTION SortingAlphanumeric(ColValue NVARCHAR(255)) 
    
    RETURNS NVARCHAR(1000) 
    
    BEGIN 
    DECLARE p1 NVARCHAR(255); 
    DECLARE p2 NVARCHAR(255); 
    DECLARE p3 NVARCHAR(255); 
    DECLARE p4 NVARCHAR(255); 
    DECLARE Index1 TINYINT; 
    
    IF ColValue LIKE '[a-z]%' THEN 
    SELECT Index1 = LOCATE('%[0-9]%', ColValue), 
    p1 = LEFT(CASE WHEN Index1 = 0 THEN ColValue ELSE LEFT(ColValue, Index1 - 1) END + REPLICATE(' ', 255), 255),
     ColValue = CASE WHEN Index1 = 0 THEN '' ELSE SUBSTRING(ColValue, Index1, 255) END;
     ELSE 
    SELECT p1 = REPLICATE(' ', 255); 
    
    SELECT Index1 = LOCATE('%[^0-9]%', ColValue); 
    
    END IF; 
    IF Index1 = 0 THEN 
    SELECT p2 = RIGHT(REPLICATE(' ', 255) + ColValue, 255), 
    ColValue = ''; 
    ELSE 
    SELECT p2 = RIGHT(REPLICATE(' ', 255) + LEFT(ColValue, Index1 - 1), 255), 
    ColValue = SUBSTRING(ColValue, Index1, 255); 
    
    SELECT Index1 = LOCATE('%[0-9,a-z]%', ColValue); 
    END IF; 
    
    IF Index1 = 0 THEN 
    SELECT p3 = REPLICATE(' ', 255); 
    ELSE 
    SELECT p3 = LEFT(REPLICATE(' ', 255) + LEFT(ColValue, Index1 - 1), 255), 
    ColValue = SUBSTRING(ColValue, Index1, 255); 
    END IF; 
    
    IF LOCATE('%[^0-9]%', ColValue) = 0 THEN 
    SELECT p4 = RIGHT(REPLICATE(' ', 255) + ColValue, 255); 
    ELSE 
    SELECT p4 = LEFT(ColValue + REPLICATE(' ', 255), 255); 
    END IF; 
    RETURN (p1 + p2 + p3 + p4); 
    
    END;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly there is no REPLICATE function in MySQL. Secondly, what you are returning is a CONCAT of the p1, p2, p3 and p4 strings. The '+' operator is does not concatenate. In fact it will actually convert the first set of digits to a number and return this:

    Code:
    mysql> select '123'+'abc';
    +-------------+
    | '123'+'abc' |
    +-------------+
    |         123 | 
    +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select 'abc'+'123';
    +-------------+
    | 'abc'+'123' |
    +-------------+
    |         123 | 
    +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select 'abc'+'123'+'932';
    +-------------------+
    | 'abc'+'123'+'932' |
    +-------------------+
    |              1055 | 
    +-------------------+
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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