asked on

convert amount(double) to india rupee format( comma separted string).

Hi,

I have one field called 'Amount' in my sql server db's table. it's datatype is double.

i want to make one user defined function which take double as argument and returns string.

basically, i want to pass amount in the user defined function that will return me currency in Indian Rupee format (comma separted string). Please keep in mind that this format is different from US $ format.

for example. if i pass 9123456789.00 to the user defined function. it should return me 9,12,34,56,789.00

output should be string datatype.

can anybody help me?

Guy Hengel [angelIII / a3]

what about this:
convert (varchar(20), convert(money, amount), 1 )

covisible

ASKER

It's returning US$ format. it's not giving output in INDIAN RUPEE format. I have clearly mention in my question that it should not return US $ format.

imran_fast

use function
Create function dbo.convertmoney (@sting varchar(100))
returns
varchar(100)
as
begin
declare @ptr int,
      @str varchar(100),
      @ptr2 int,
      @rup int,
      @coma int
set @ptr = len(@sting)
set @ptr2= 0
while right(@sting,@ptr2) not like '.%' and @ptr2 < @ptr
begin
set @ptr2= @ptr2 + 1
end
set @str = '.00'
set @ptr = @ptr-@ptr2
set @coma = 0
set @rup = 0
while @ptr >0
begin
      if @rup is null
      begin
      if @coma = 2
      begin set @str = ','+@str set @coma = 0 end
      set @coma = @coma + 1
      set @str = substring(@sting,@ptr,1)+@str
      set @ptr = @ptr -1
      end
      else
            begin
                  if @rup = 3
                  begin set @str = ','+@str set @rup = null end
                  set @rup = @rup + 1
                  set @str = substring(@sting,@ptr,1)+@str
                  set @ptr = @ptr -1
            end

end

return @str
end
go

select dbo.convertmoney('9123456789.00')

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

covisible

ASKER

Thanks very much