

SSC Veteran
Group: General Forum Members
Last Login: Wednesday, June 19, 2013 11:21 PM
Points: 230,
Visits: 157


hi... to all i've created an report in that report i've one salary field now i want to display the total amount in indian currency format e.g {12,12,12,12,12,123.00} and also in words such as NINETY NINE CRORES NINETY NINE LAKHS NINETY NINE THOUSAND NINE HUNDRED AND NINTY NINE RUPEES ONLY. if after decimal point there's some value then it should be like fourty three paise only
please help me...




SSChampion
Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905,
Visits: 32,158


it's much easier to adapt a wheel than it is to reinvent one. here is a function that takes a decimal and returns it as words in english. you could easily adapt it to your requirements.
I got this from bitbucket a year ago, who in turn harvested it from other forum posts by unnamed contributors:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfn_NumberToWords]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'/* Name: ctfn_NumberToWords Version: 1.0 Date: 21Feb2007 Description: Takes an integer and converts it to words by storing the integer into a table three digits at a time as hundreds, tens and a factorial multiplier ''units''.
Returns a VARCHAR(2000) string containing the text of the number, e.g.: ONE THOUSAND ONE HUNDRED AND TWENTY FOUR Returns MINUS for a minus number and NULL if there is an error
Usage: SELECT dbo.ctfn_NumberToWords(INTEGER) SELECT dbo.ctfn_NumberToWords(1787.55) + '' DOLLARS '' + dbo.ctfn_NumberToWords((1787.55  CAST(1787.55 AS INT)) * 100) + '' CENTS.''
gives
ONE THOUSAND SEVEN HUNDRED AND EIGHTY SEVEN DOLLARS FIFTY FIVE CENTS.
David le Quesne
 History
1.0 DLQ 21022007 RFC001428 Function created for ATPs
*/
CREATE FUNCTION [dbo].[udfn_NumberToWords] (@intNumberValue INTEGER) RETURNS VARCHAR(2000) AS BEGIN DECLARE @strNumberString VARCHAR(9) DECLARE @strReturn VARCHAR(2000) DECLARE @intUnits SMALLINT
 Create table of number groups DECLARE @tblNumberGroups TABLE (Units SMALLINT, Hundreds SMALLINT, Tens SMALLINT)
 Handle errors and ''quick wins'' IF @intNumberValue IS NULL RETURN NULL IF ISNUMERIC(@intNumberValue)=0 RETURN NULL IF @intNumberValue = 0 RETURN ''ZERO''
IF @intNumberValue < 0 BEGIN SET @strReturn=''MINUS '' SET @intNumberValue=ABS(@intNumberValue) END
SET @intUnits =0
 Populate table of number groups WHILE (@intNumberValue % 1000) > 0 OR (@intNumberValue/1000) >0 BEGIN INSERT INTO @tblNumberGroups (Units, Hundreds, Tens) VALUES (@intUnits, (@intNumberValue % 1000)/100, (@intNumberValue % 1000) % 100 ) SELECT @intNumberValue = CAST (@intNumberValue / 1000 AS INTEGER) SET @intUnits = @intUnits + 1 END
 Remove last unit added SET @intUnits = @intUnits1
 Concatenate text number by reading number groups in reverse order SELECT @strReturn = ISNULL(@strReturn,'' '') + ISNULL( ISNULL((CASE Hundreds WHEN 1 THEN ''ONE HUNDRED '' WHEN 2 THEN ''TWO HUNDRED '' WHEN 3 THEN ''THREE HUNDRED '' WHEN 4 THEN ''FOUR HUNDRED '' WHEN 5 THEN ''FIVE HUNDRED '' WHEN 6 THEN ''SIX HUNDRED '' WHEN 7 THEN ''SEVEN HUNDRED '' WHEN 8 THEN ''EIGHT HUNDRED '' WHEN 9 THEN ''NINE HUNDRED '' END),'' '') + CASE WHEN (Hundreds >0 OR Units<@intUnits) AND Tens > 0 THEN '' AND '' ELSE '' '' END + ISNULL((CASE Tens / 10 WHEN 2 THEN ''TWENTY '' WHEN 3 THEN ''THIRTY '' WHEN 4 THEN ''FORTY '' WHEN 5 THEN ''FIFTY '' WHEN 6 THEN ''SIXTY '' WHEN 7 THEN ''SEVENTY '' WHEN 8 THEN ''EIGHTY '' WHEN 9 THEN ''NINETY '' END),'' '') + ISNULL((CASE Tens WHEN 10 THEN ''TEN '' WHEN 11 THEN ''ELEVEN '' WHEN 12 THEN ''TWELVE '' WHEN 13 THEN ''THIRTEEN '' WHEN 14 THEN ''FOURTEEN '' WHEN 15 THEN ''FIFTEEN '' WHEN 16 THEN ''SIXTEEN '' WHEN 17 THEN ''SEVENTEEN '' WHEN 18 THEN ''EIGHTEEN '' WHEN 19 THEN ''NINETEEN '' END),'' '') + COALESCE( CASE WHEN Tens %10 =1 AND Tens / 10 <> 1 THEN ''ONE '' END, CASE WHEN Tens %10 =2 AND Tens / 10 <> 1 THEN ''TWO '' END, CASE WHEN Tens %10 =3 AND Tens / 10 <> 1 THEN ''THREE '' END, CASE WHEN Tens %10 =4 AND Tens / 10 <> 1 THEN ''FOUR '' END, CASE WHEN Tens %10 =5 AND Tens / 10 <> 1 THEN ''FIVE '' END, CASE WHEN Tens %10 =6 AND Tens / 10 <> 1 THEN ''SIX '' END, CASE WHEN Tens %10 =7 AND Tens / 10 <> 1 THEN ''SEVEN '' END, CASE WHEN Tens %10 =8 AND Tens / 10 <> 1 THEN ''EIGHT '' END, CASE WHEN Tens %10 =9 AND Tens / 10 <> 1 THEN ''NINE '' END, '' '')+ COALESCE( CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN ''THOUSAND '' END, CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN ''MILLION '' END, CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN ''BILLION '' END, CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN ''TRILLION '' END, '' '') ,'' '') FROM @tblNumberGroups ORDER BY units DESC  Get rid of all the spaces WHILE CHARINDEX('' '', @strReturn)>0 BEGIN SET @strReturn = REPLACE(@strReturn,'' '','' '') END
SET @strReturn = LTRIM(RTRIM(@strReturn))
RETURN @strReturn END
' END
Lowell
There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. my son




Hall of Fame
Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421,
Visits: 5,359





SSC Veteran
Group: General Forum Members
Last Login: Wednesday, June 19, 2013 11:21 PM
Points: 230,
Visits: 157


Hi thanks for the reply
I got the solution ..
u can check here
[url=http://vinayakblog.blogspot.in/2011/07/indiancurrencyformatforrdlorrdlc.html][/url]



