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
