spell number - 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

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-23 : 03:03:09

hi, everybody,

i am facing with spellnumber when i try to execute ,kindly i need help

i have given below the sql coding
USE [PSBC_DB1]
GO
/****** Object: UserDefinedFunction [dbo].[fn_spellNumber] Script Date: 10/22/2013 4:35:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---SELECT DBO.fn_spellNumber ('22,33,74,555.29','L',1,1)
---SELECT DBO.fn_spellNumber ('22,33,84,555.29','L',1,2)

--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)

--******************
declare @v_SpellNumber decimal
declare @strCents varchar(100)
declare @strnum varchar(100)
declare @intIndex integer

select @SpellNumber=replace(@SpellNumber,',','')
select @v_SpellNumber = convert(decimal,@SpellNumber)
Select @strnum = Cast(@SpellNumber as varchar(100))
Select @intIndex = CharIndex('.', @strnum)

--select @v_SpellNumber = convert(int,convert(decimal,@SpellNumber))
---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 = 1
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

if @flag = 2

begin
select @REV = REVERSE(SUBSTRING(@Res+@strCents,1,60))
select @v_f2= len(@rev)--len(REVERSE(SUBSTRING(@REV,CHARINDEX(' ',@REV)+1,60)))
--set @v_aftersixty=SUBSTRING(@Res + @strCents,60,@v_len)--LEN(@StrFinal + ' '+ @strCents))
select @v_aftersixty=SUBSTRING(@Res+@strCents,@v_f2+1,LEN(@Res+@strCents))

set @Res1 = @v_aftersixty
end
-----*******************************************************
SELECT @Res1 = LTRIM ( RTRIM(@Res1 ))
RETURN @Res1
END

---SELECT DBO.fn_spellNumber ('22,33,74,555.29','L',1,1)
---SELECT DBO.fn_spellNumber ('22,33,84,555.29','L',1,2)

when we execute first one .getting answer like this -Twenty Two Crore Thirty Three Lakh Seventy Four Thousand Fiv

when we execute second one ---getting answer like this --
e Hundred And Fifty Five Twenty Nine Cents

we want result 1): before the space limit 60 character: Twenty Two Crore Thirty Three Lakh Seventy Four Thousand
2)after the character : Hundred And Fifty Five Twenty Nine Cents

result would be round character figure

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

any one pls reply

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-23 : 05:32:34

Duplicate thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189060

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources