October 8, 2010 at 7:01 am
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...
October 8, 2010 at 3:21 pm
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
February 12, 2013 at 8:01 pm
You could use this approach to format the digits groupings:
A SQL-Based Universal Currency Formatter[/url]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2013 at 9:41 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply