Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Total indian currency format Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, October 08, 2010 7:01 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, June 19, 2013 11:21 PM Points: 230, Visits: 157
 hi... to alli've created an report in that report i've one salary fieldnow 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 asNINETY 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 onlyplease help me...
Post #1001251
 Posted Friday, October 08, 2010 3:21 PM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 4:34 PM Points: 12,755, Visits: 31,122
 it's much easier to adapt a wheel than it is to re-invent 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 ONGOSET QUOTED_IDENTIFIER ONGOIF 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'))BEGINexecute dbo.sp_executesql @statement = N'/*---------------------------------------------------------------------------------------------------------------------- Name: ctfn_NumberToWords Version: 1.0 Date: 21-Feb-2007 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 21-02-2007 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 = @intUnits-1 -- 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 @strReturnEND' 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
Post #1001626
 Posted Tuesday, February 12, 2013 8:01 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 5:39 PM Points: 3,596, Visits: 5,113
 You could use this approach to format the digits groupings:A SQL-Based Universal Currency Formatter My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1419273
 Posted Tuesday, February 12, 2013 9:41 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, June 19, 2013 11:21 PM Points: 230, Visits: 157
 Hi thanks for the replyI got the solution .. u can check here [url=http://vinayak-blog.blogspot.in/2011/07/indian-currency-format-for-rdl-or-rdlc.html][/url]
Post #1419287

 Permissions