Hi, this should help:

CREATE FUNCTION FormatLakhCrores (@number SQL_VARIANT,@conversion smallint)

RETURNS TABLE

WITH SCHEMABINDING

AS

--= Function to format a number using the Lakh/Crores style

--= Written 06 Sept 2013 Mister Magoo

--= Permission granted for public re-use for any purpose except derision

--= Parameter @number : pass in a numeric/decimal/integer/money type

--= Parameter @conversion : pass in a suitable conversion style for the CONVERT function - if in doubt pass 0 (zero) (a value of 2 with the money data type will force 4 decimal places)

--= Returns a varchar value containing the formatted number

RETURN

-- the returned value is built up by stripping the number down and inserting commas where required, then concatenating the digits

SELECT

-- because the comma insertion works from right to left, we need to reverse the result to see the required string

REVERSE((

-- the case statement figures out where to insert a comma

-- we need a comma before the last three digits before the decimal point

-- but we are working from right to left, so they are the first three after the point.

-- subsequently we need a comma every two digits

SELECT CASE

-- all digits after the last/first comma are in this group

WHEN N-scale < 5

THEN ''

-- the last/first comma goes here

WHEN N-scale = 5

THEN ','

-- capture every second digit here

WHEN (N-scale) % 2 = 1

THEN ','

ELSE ''

END

-- and grab the digit at this position in the string

+ SUBSTRING(string, LEN(string) - N+1, 1)

FROM

-- Build an inline tally table which contains 49 numbers - more than enough for a decimal(38)

(

SELECT ROW_NUMBER() OVER(ORDER BY @conversion) -- the order by is irrelevant

FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a7(N)

,(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) b7(N)

) AS Tally49(N)

CROSS APPLY

-- Use a cross apply construct to alias a couple of expressions

(

SELECT

-- our plain formatted number with any pre-existing commas stripped

replace(convert(VARCHAR(1000), @number, @conversion),',','') AS string

-- figure where the decimal point is - if any is present

,CHARINDEX('.',reverse(replace(convert(VARCHAR(1000), @number, @conversion),',','')))-1

) x(string, scale)

-- only use as much of the Tally table as we need

WHERE N< = len(string)

ORDER BY N

-- and concatenate the results using for xml path()

FOR XML PATH('')

)) AS FormattedNumber

Use it like this for "money" :

declare @test-2 money = 1000000000;

select FormattedNumber

from FormatLakhCrores(@test,0)

Or from a table:

select some_column,FormattedNumber

from SomeTable

cross apply dbo.FormatLakhCrores(some_column,0)