spell number urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

spell number urgent

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-29 : 03:02:11

hi everyone,
i facing problem in spell number query,
i need help,
query is running sucessfully,but ineed to add (and)after the decimal value
which i have given query below,

1)select dbo.fn_spellnumber('12,34,56,789.35','L',1,0)
-------
getting result
-------
Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred And Eighty Nine Thirty Five Cents

my question is the result would be
Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred And Eighty Nine and Thirty Five Cents

2)select dbo.fn_spellnumber('22,34,65,234.10','L',1,0)
result
------
Twenty Two Crore Thirty Four Lakh Sixty Five Thousand Two Hundred And Thirty Four Ten Cents

i need this below answer
Twenty Two Crore Thirty Four Lakh Sixty Five Thousand Two Hundred And Thirty Four and Ten Cents (i want to add and after the decimal value)

the coding has given below

USE [PSBC_DB1]
GO
/****** Object: UserDefinedFunction [dbo].[fn_spellNumber] Script Date: 10/29/2013 10:31:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Twenty Two Crore Twenty Three Lakh Thirty Three Thousand Four Hundred And Fourty Five Fifty Five Cents
ALTER FUNCTION [dbo].[fn_spellNumber](@SpellNumber varchar(100),
@SpellType VARCHAR(10) = 'L',
@AddComma BIT = 1,
@flag int =1)
RETURNS VARCHAR(1000)
bEGIN

DECLARE @Res VARCHAR(MAX)
DECLARE @TmpRes VARCHAR(MAX)
DECLARE @CurrentNumber int
DECLARE @PrevNumber int
DECLARE @Comma VARCHAR(1)
DECLARE @Loopvar int
DECLARE @VarSpellNumber VARCHAR(50)

--******************
---SELECT DBO.fn_spellNumber ('2000.55','L',1,1)
declare @v_SpellNumber decimal
declare @strCents varchar(100)
declare @strnum varchar(100)
declare @intIndex integer

set @SpellNumber=replace(@SpellNumber,',','')
--select convert(decimal(9,2),'2000.55')
set @v_SpellNumber = convert(decimal,@SpellNumber)
Set @strnum = Cast(@SpellNumber as varchar(100))
Set @intIndex = CharIndex('.', @strnum)

--select @v_SpellNumber = convert(int,convert(decimal,@SpellNumber))
---select @v_SpellNumber=substring(@SpellNumber,1,CharIndex('.', @SpellNumber)-1)
select @v_SpellNumber= substring(@SpellNumber,1,CharIndex('.', @SpellNumber)-1)
select @strCents = ''

If @intIndex > 0
begin

Select @strCents = dbo.fConvertTens(Right(@strnum, Len(@strnum) - @intIndex))
Select @strnum = SubString(cast(@strnum as varchar), 1, Len(@strnum) - 3)
If Len(@strCents) > 0 Select @strCents = @strCents + ' Cents'

end
--*******************

IF @v_SpellNumber < 0
SELECT @SpellNumber = @v_SpellNumber * -1

SELECT @VarSpellNumber = @v_SpellNumber

SELECT @Loopvar = 1
SELECT @Res = ''

SELECT @Comma = CASE @AddComma
WHEN 1 THEN ','
ELSE ''
END

WHILE LTRIM(RTRIM(@VarSpellNumber)) <> ''
BEGIN

SELECT @CurrentNumber = NULL
SELECT @PrevNumber = NULL
SELECT @TmpRes = ''

SELECT @CurrentNumber = SUBSTRING(@VarSpellNumber,LEN(@VarSpellNumber),1)

IF LEN(@VarSpellNumber) > 1
SELECT @PrevNumber = SUBSTRING(@VarSpellNumber,LEN(@VarSpellNumber)-1,1)

IF (@Loopvar = 2 AND @SpellType = 'L') OR (@Loopvar%2=0 AND @SpellType ='M')
SELECT @PrevNumber = NULL

IF @PrevNumber IS NULL OR @PrevNumber <> 1
BEGIN
SELECT @TmpRes = CASE @CurrentNumber
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three'
WHEN 4 THEN 'Four'
WHEN 5 THEN 'Five'
WHEN 6 THEN 'Six'
WHEN 7 THEN 'Seven'
WHEN 8 THEN 'Eight'
WHEN 9 THEN 'Nine'
WHEN 0 THEN ''
END
END

IF @PrevNumber = 1
BEGIN
SELECT @TmpRes = CASE CONVERT(INT,CONVERT(VARCHAR(1),@PrevNumber) + CONVERT(VARCHAR(1),@CurrentNumber))
WHEN 10 THEN 'Ten'
WHEN 11 THEN 'Eleven'
WHEN 12 THEN 'Twelve'
WHEN 13 THEN 'Thirteen'
WHEN 14 THEN 'Fourteen'
WHEN 15 THEN 'Fiveteen'
WHEN 16 THEN 'Sixteen'
WHEN 17 THEN 'Seventeen'
WHEN 18 THEN 'Eighteen'
WHEN 19 THEN 'Nineteen'
END

END

IF @PrevNumber > 1
BEGIN
SELECT @TmpRes = CASE @PrevNumber
WHEN 2 THEN 'Twenty'
WHEN 3 THEN 'Thirty'
WHEN 4 THEN 'Fourty'
WHEN 5 THEN 'Fifty'
WHEN 6 THEN 'Sixty'
WHEN 7 THEN 'Seventy'
WHEN 8 THEN 'Eighty'
WHEN 9 THEN 'Ninety'
WHEN 0 THEN ''
END + ' ' + @TmpRes
END

SELECT @TmpRes = LTRIM(RTRIM(@TmpRes))

IF @SpellType ='L' AND (@TmpRes <> '' OR ( @Loopvar = 5 AND CONVERT(INT,@VarSpellNumber)>0))
BEGIN
SELECT @TmpRes = @TmpRes + ' '
+ CASE @Loopvar
WHEN 2 THEN 'Hundred'
WHEN 3 THEN 'Thousand' + @Comma
WHEN 4 THEN 'Lakh' + @Comma
WHEN 5 THEN 'Crore' + @Comma
ELSE ''
END

END

IF @SpellType ='M' AND (@TmpRes <> '' OR ( @Loopvar%2 = 1 AND CONVERT(INT,SUBSTRING(@VarSpellNumber,LEN(@VarSpellNumber)-2,1))>0))
BEGIN
SELECT @TmpRes = @TmpRes + ' '
+ CASE @Loopvar
WHEN 2 THEN 'Hundred'
WHEN 3 THEN 'Thousand' + @Comma
WHEN 4 THEN 'Hundred'
WHEN 5 THEN 'Million' + @Comma
WHEN 6 THEN 'Hundred'
WHEN 7 THEN 'Billion' + @Comma
WHEN 8 THEN 'Hundred'
WHEN 9 THEN 'Trillion' + @Comma
WHEN 10 THEN 'Hundred'
WHEN 11 THEN 'Quadrillion' + @Comma
WHEN 12 THEN 'Hundred'
WHEN 13 THEN 'Quintillion' + @Comma
ELSE ''
END
END

IF @Loopvar = 1 AND LEN(@VarSpellNumber) > 2
IF CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(50),@v_SpellNumber),LEN(@VarSpellNumber)-1,2)) > 0
SELECT @TmpRes = 'And ' + @TmpRes

IF @TmpRes <> ''
SELECT @Res = @TmpRes + ' ' + @Res

IF (@Loopvar = 2 AND @SpellType = 'L') or (@Loopvar%2=0 AND @SpellType ='M')
SELECT @VarSpellNumber = SUBSTRING(@VarSpellNumber,1,LEN(@VarSpellNumber)-1)
ELSE IF LEN(@VarSpellNumber) = 1
SELECT @VarSpellNumber = SUBSTRING(@VarSpellNumber,1,LEN(@VarSpellNumber)-1)
ELSE
SELECT @VarSpellNumber = SUBSTRING(@VarSpellNumber,1,LEN(@VarSpellNumber)-2)

SELECT @Loopvar = @Loopvar + 1

IF @Loopvar = 6 AND @SpellType ='L'
SELECT @Loopvar = 2

END

-----*******************************************************
DECLARE @REV VARCHAR(MAX);
declare @v_firstsixty varchar(max);
declare @v_aftersixty varchar(max);
declare @Res1 varchar(500);
declare @v_len int;
declare @v_f2 int;
SELECT @Res = replace(ISNULL(@Res,''),',','')
set @v_len= LEN(@Res + @strCents)

if @flag = 0
BEGIN
SET @Res1 =@Res + @strCents
end

if @flag = 1
begin
SET @REV = @Res + @strCents
set @Res1 = (@REV)

if @v_len <= 60
begin

SET @REV = SUBSTRING(@Res + @strCents ,1,60)
set @Res1 = (@REV)
end
else
begin

SET @REV = REVERSE(SUBSTRING(@Res + @strCents,1,60))
set @v_firstsixty=REVERSE(SUBSTRING(@REV,CHARINDEX(' ',@REV)+1,LEN(@REV)))
set @Res1 = @v_firstsixty
end
end
if @flag = 2

Begin
declare @char int

set @REV = REVERSE(SUBSTRING(@Res+@strCents,1,60))

set @char=len(REVERSE(SUBSTRING(@REV,CHARINDEX(' ',@REV)+1,60)))
set @v_f2= len(@rev)

if @v_f2>120
begin
--select @v_aftersixty=SUBSTRING(@Res+@strCents,@v_f2+10,@v_f2*2)
set @v_aftersixty=case when @v_f2<60 then '' else SUBSTRING(@Res+@strCents,@v_f2+10,@v_f2*2) end

end
else if @v_f2>60 or @v_f2<120
begin

set @v_aftersixty=case when @v_f2<60 then '' else SUBSTRING(@Res+@strCents,@char+1,@v_f2*2) end

end
else if @v_f2<60
begin

set @v_aftersixty=''

end
else
begin
--select @v_aftersixty=SUBSTRING(@Res+@strCents,@v_f2+10,@v_f2*2)
set @v_aftersixty=case when @v_f2<60 then '' else SUBSTRING(@Res+@strCents,@v_f2+10,@v_f2*2) end

end
set @Res1 = @v_aftersixty
End

-----*******************************************************
SET @Res1 = LTRIM ( RTRIM(@Res1 ))
RETURN @Res1
END

----------------------------------------

-- select dbo.fn_spellnumber('12,34,56,789.35','L',1,0)
--

--- select dbo.fn_spellnumber('22,34,65,234.10','L',1,0)
---

thanks
its urgent pls....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-10-29 : 08:30:55

see if this is what you are looking for:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151482

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-29 : 08:50:01

thank you so much sir i am trying with your code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources