Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Total indian currency format Expand / Collapse
Author
Message
Posted Friday, October 8, 2010 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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...
Post #1001251
Posted Friday, October 8, 2010 3:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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 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: 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 @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
Post #1001626
Posted Tuesday, February 12, 2013 8:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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://vinayak-blog.blogspot.in/2011/07/indian-currency-format-for-rdl-or-rdlc.html][/url]
Post #1419287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse