MySQL funcation for Indian rupee comma separator format (eg: 85,27,41,963.23)

The following MySQL function help you to format a number into Indian rupee comma separator style.

 If you give  852741963.23 the function out put look like this  85,27,41,963.23

select RupeeFormat(852741963.23)

Out put : 85,27,41,963.23

CREATE  FUNCTION `RupeeFormat`(`nAmount` DECIMAL(12,2))
    RETURNS varchar(200) CHARSET utf8
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

  DECLARE res VARCHAR(50);
  DECLARE p1  VARCHAR(40);
  DECLARE p2  VARCHAR(10);

  SET res = REPLACE(CAST(FORMAT(nAmount,2) AS CHAR(20) ),',','');
  SET p1  = LEFT(res, INSTR(res,'.')-1);
  SET p2  = SUBSTRING(res, INSTR(res,'.'), 10);

  SET res = CONCAT(RIGHT(p1, 3), p2);
  SET p1  = LEFT(p1, LENGTH(p1)-3);

  WHILE (p1<>'') DO
 
    SET res = CONCAT(RIGHT(p1, 2) , ',' , res);
   
    IF (LENGTH(p1) > 2) THEN
   
      SET p1 = LEFT(p1, LENGTH(p1)-2);
     
    ELSE
   
      SET p1= '';
     
    END IF;
  END WHILE;

  RETURN res;

END