mysql function

[mancode1007]

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 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 ;

[smantscheff]

So where is the problem?

[mancode1007]

not allowed to return a result set from a function

[pritaeas]

Have you tried declaring a variable with the same type as the return result, putting your concatenation in it, and RETURN the variable?

Second, aren't you supposed to end with:

END $$

DELIMITER ;

[mancode1007]

still the same

[kartisathis]

Hi i think there is a mistake in assigning values,

mysql> select @a=locate('a','karthik');
    -> $$
+--------------------------+
| @a=locate('a','karthik') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

but the actual result is,

mysql> select @a:=locate('a','karthik');
    -> $$
+---------------------------+
| @a:=locate('a','karthik') |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

so better try this way of assigning values in select query

SELECT  LOCATE('%[0-9]%', ColValue) into Index1 .....

I tink it works :P..