Get Float value with Comma separtor

Question

Am new to Sql server ce. my table have float value. I want select float with comma separated and decimal position. like this

table name table1

I want result like this

val
12,20,333.00
222.00
36,535.00

like indian Rupees

Am using Sql server ce 3.5

General advice is to format your data in the presentation layer. — David Rushton, Dec 22 '15 at 12:53
@destination-data i need load this value in drop down. so only i tried in query — Sathish, Dec 22 '15 at 13:06
Check this link and hope it helps you: http://stackoverflow.com/questions/12492567/how-do-i-convert-string-to-indian-money-format — Paresh J, Dec 22 '15 at 13:08

mohan111 · Accepted Answer · 2015-12-22T13:17:52.380

1

DECLARE @Table1 TABLE 
    (val int)
;

INSERT INTO @Table1
    (val)
VALUES
    (1220333),
    (222),
    (36535)
;
select convert(varchar(50), CAST(val as money), -1) amount from @Table1

select FORMAT(CAST(val AS MONEY),'N','en-in') amount from @Table1

OR

Function

create function to_indian_currency(@n decimal(25,5))
returns varchar(100) as
BEGIN
declare @a varchar(100) = cast(@n as varchar(100))
declare @dec_part varchar(100) = 
(select substring(@a, charindex('.',@a), len(@a)-charindex('.',@a)+1))
declare @int_part varchar(100) = (select left(@a, charindex('.',@a)-1))
declare @f int = cast(@int_part as bigint)%1000
declare @q int = cast(@int_part as bigint)/1000
declare @final varchar(100) = ''
while @q > 0
begin
set @final = cast(@q%100 as varchar) + ',' + @final
set @q = @q/100
end
RETURN @final + cast(@f as varchar) + @dec_part
END

select dbo.to_indian_currency(val) from @Table1

edited Dec 22 '15 at 13:17

answered Dec 22 '15 at 13:02

mohan111

8,633
4
28
55

`1,220,333.00` and OP wants `12,20,333.00` – Lukasz Szozda Dec 22 '15 at 13:03
After adding locale it is OK . But I am not sure that FORMAT is available in [SQL Server CE](https://technet.microsoft.com/en-us/library/ms174077%28v=sql.110%29.aspx) – Lukasz Szozda Dec 22 '15 at 13:10
1

Its Show Error. The Function not recognized. Function name Format – Sathish Dec 22 '15 at 13:15
now try this function it will work in CE 3.5 @Sathish – mohan111 Dec 22 '15 at 13:22

Get Float value with Comma separtor

1 Answers1