Start New Discussion within our Software Development Community

Guys, I having problem converting the sql server function to mysql function. It work perfect at sql server now it now working at mysql. Please help.Thanks

DELIMITER $$ 

CREATE Function  `pearldb`.`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 
This article has been dead for over six months. Start a new discussion instead.