SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SPELL NUMBER ISSUE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajnidas
Yak Posting Veteran

India
72 Posts

Posted - 11/29/2013 :  07:29:04  Show Profile  Reply with Quote
Hi everyone,

i am facing problem in spell number(scalarfunction)
query has given below;
========================================
declare @Res1 varchar(500) = '';
declare @v_len int;
declare @v_f2 int;

Begin

SELECT @Res = replace(ISNULL(@Res,''),',','');
set @v_len= LEN(@Res + @strCents);

if @flag = 0
SET @Res1 =@Res + @strCents;

else if @flag = 1
if @v_len < 60
SET @Res1 = SUBSTRING(@Res + @strCents ,1, @v_len);
else
set @Res1 = SUBSTRING(@Res + @strCents ,1, 60);




else if @flag = 2 AND @v_len > 60
set @Res1 = SUBSTRING(@Res + @strCents ,61, @v_len)




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

RETURN @Res1
End
END



Result is
-------------
1. select dbo.fn_spellnumber('12,34,56,789.35','L',1,0)
result:Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred And Eighty Nine And Thirty Five Paise
2.select dbo.fn_spellnumber('12,34,56,789.35','L',1,1)
result:Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundr
3.select dbo.fn_spellnumber('12,34,56,789.35','L',1,2)
result: ed And Eighty Nine And Thirty Five Paise


i need result would be round figure words ,in 2 and 3 . no need to cut the words.


pls i need help its urgent

ijmar86
Starting Member

India
8 Posts

Posted - 11/30/2013 :  00:51:39  Show Profile  Reply with Quote
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Spell a number in UK format
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.fn_spellNumber(1234567890,'UK',0)
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Spell a number in US format
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.fn_spellNumber(1234567890,'US',0)

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Spell a number in US format with comma
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.fn_spellNumber(1234567890,'US',1)

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- Sample for how the function can be used with SELECT
-- Note : Some value is getting rounded off as the function takes Integer as input.
-------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT TOP 10 [Rate]
,dbo.fn_spellNumber(substring(CONVERT(VARCHAR(50),[Rate]),1,CHARINDEX('.',CONVERT(VARCHAR(50),[Rate]))-1),'US',0) + ' Dollars ' +
dbo.fn_spellNumber(substring(CONVERT(VARCHAR(50),[Rate]),CHARINDEX('.',CONVERT(VARCHAR(50),[Rate]))+1,2),'US',1) + ' Cents'
FROM [AdventureWorks2008R2].[HumanResources].[EmployeePayHistory]

Edited by - ijmar86 on 11/30/2013 00:52:07
Go to Top of Page

ijmar86
Starting Member

India
8 Posts

Posted - 11/30/2013 :  00:57:56  Show Profile  Reply with Quote
SET NOCOUNT ON

-- =============================================
-- Author: Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description: Converts an integer into words, viz.,
-- master.dbo.fnSpellInteger(10129) ==> "Ten Thousand One Hundred Twenty-Nine"
-- =============================================
USE MASTER

IF OBJECT_ID('dbo.fnSpellInteger') IS NOT NULL DROP FUNCTION fnSpellInteger
GO

CREATE FUNCTION dbo.fnSpellInteger ( @number int )
RETURNS VARCHAR(100)
AS
BEGIN
-- For debugging outside of the UDF: DECLARE @debug bit SET @debug = 0

DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100)
DECLARE @i int, @j int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)

IF @number = 0 RETURN 'Zero'

SELECT @result = '', @word = '', @group = ''

SET @cn = @number
SET @cn = REPLACE(@cn,',','')
SET @m = LEN(@cn) % 3
IF @m > 0 SET @cn = REPLICATE('0',3-@m) + @cn -- Left pad with zeroes to a multiple of 3

SET @i = 1
SET @j = LEN(@cn)-@i+1
SET @m = @i % 3
WHILE @i <= LEN(@cn)
BEGIN
-- @i is 1 origin index into numeric string while @m = @i modulo 3
-- If the middle digit of each group of 3 is a '1' then this is a 'Ten' or a '...teen'
IF @m = 2 AND SUBSTRING(@cn,@i,1) = '1'
BEGIN
SET @digit = SUBSTRING(@cn,@i,2)
-- Skip rightmost digit of 3 if processing teens
SET @i = @i + 1
END
ELSE
SET @digit = SUBSTRING(@cn,@i,1)

SET @word =
CASE
WHEN @m = 0 THEN -- Rightmost digit of group of 3
CASE @digit
WHEN '0' THEN ''
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'
END +
CASE
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 2 THEN ' Thousand'
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 3 THEN ' Million'
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 4 THEN ' Billion'
ELSE ''
END
WHEN LEN(@digit) = 2 THEN -- Special case when middle digit is a '1'
CASE @digit
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 +
CASE
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 2 THEN ' Thousand'
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 3 THEN ' Million'
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 4 THEN ' Billion'
ELSE ''
END
WHEN @m = 2 THEN -- Middle digit of group of 3
CASE @digit
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'
ELSE ''
END
WHEN @m = 1 THEN -- Leftmost digit of group of 3
CASE @digit
WHEN '0' THEN ''
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'
END +
CASE WHEN @digit <> '0' THEN ' Hundred' ELSE '' END
END

SET @group = @group + RTRIM(@word) -- Group value

IF @word <> ''
BEGIN
DECLARE @prefix VARCHAR(20)
IF CHARINDEX(' ',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX(' ',@word)) ELSE SET @prefix = @word
IF RIGHT(@result,2) = 'ty' AND @prefix IN ('One','Two','Three','Four','Five','Six','Seven','Eight','Nine')
SET @result = @result + '-' + LTRIM(@word)
ELSE
SET @result = @result + ' ' + LTRIM(@word)
END
-- The following needs to be outside of a UDF to work:
--IF @debug = 1 SELECT @cn as 'Number', @i as '@i', @j as '@j', @m as '@m', @digit as '@digit', CAST(replace(@group,' ','`') AS CHAR(30)) as '@group', @word as '@word', @result as '@result'
SET @i = @i + 1
SET @j = LEN(@cn)-@i+1
SET @m = @i % 3
IF @m = 1 SET @group = '' -- Clear group value when starting a new one

END

IF @result = '' SET @result = '0'
RETURN LTRIM(@Result)

END
GO


SET NOCOUNT ON

-- =============================================
-- Author: Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description: Converts a string numeric expression into words, viz.,
-- master.dbo.fnSpellNumber('15.99') ==> "Fifteen and Ninety-Nine Hundredths"
-- Notes: Uses fnSpellInteger to convert an integer into words
-- Example: fnSpellNumber can be used to generate pseudo-random test character data
/*
set nocount on
declare @rand int, @i int
set @rand = rand(131)*100
set @i = 0

while @i < 5
begin
set @i = @i + 1
select 'insert(id,number,words) values(' + cast(@i as varchar(5)) + ',' +
cast(@rand as varchar(5)) + ',''' + master.dbo.fnspellnumber(@rand) + ''')'
set @rand = rand()*100
end
*/
-- =============================================
USE MASTER

IF OBJECT_ID('master.dbo.fnSpellNumber') IS NOT NULL DROP FUNCTION fnSpellNumber
GO

CREATE FUNCTION dbo.fnSpellNumber ( @number varchar(20) )
RETURNS VARCHAR(200)
AS
--For debugging: declare @number varchar(20) set @number = '192.1'
BEGIN
-- This is for use outside of a function: DECLARE @debug bit SET @debug = 0

DECLARE @result varchar(200), @word varchar(100)
DECLARE @i int, @intpart varchar(20), @decpart varchar(20)

SET @word = LTRIM(RTRIM(@number))
-- Check for a bad number, e.g., one with embedded spaces
IF ISNUMERIC(@word) = 0 RETURN '<< NOT A NUMBER >>'

SET @i = CHARINDEX('.', @word)
-- Remove trailing zeroes for any decimal portion
IF @i > 0 -- Number contains a decimal point
BEGIN
WHILE RIGHT(@word,1) = '0' SET @word = LEFT(@word,LEN(@word)-1)
IF @word = '' SET @word = '0'
END
-- Insert a decimal point at the end if none was specified
IF @i = 0 -- No decimal point
BEGIN
SET @word = @number + '.'
SET @i = CHARINDEX('.', @word)
END

SET @intpart = LEFT(@word,@i-1) -- Extract the integer part of the number if any
IF LEN(@intpart) > 0
SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int))
ELSE
SET @result = ''

-- Extract the decimal portion of the number
SET @decpart = RIGHT(@word,LEN(@word)-@i) -- @i is position of decimal point

IF LEN(@decpart) > 0
BEGIN
IF @result = 'Zero'
SET @result = ''
ELSE IF @result <> ''
SET @result = @result + ' and '

SET @result = @result + master.dbo.fnSpellInteger(@decpart) +
CASE LEN(@decpart)
WHEN 0 THEN ''
WHEN 1 THEN ' Tenths'
WHEN 2 THEN ' Hundredths'
WHEN 3 THEN ' One-Thousandths'
WHEN 4 THEN ' Ten-Thousandths'
WHEN 5 THEN ' One-Hundred-Thousandths'
WHEN 6 THEN ' One-Millionths'
WHEN 7 THEN ' Ten-Millionths'
WHEN 8 THEN ' One-Hundred-Millionths'
WHEN 9 THEN ' One-Billionths'
END
-- Check for a valid plural
IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1) -- Remove last "s" for just 1
END

-- This is for use outside of a function: if @debug = 1 select @word as '@word', @i as '@i', @intpart as '@intpart', @decpart as '@decpart', @result as '@result'

RETURN @result

END
GO


Go to Top of Page

rajnidas
Yak Posting Veteran

India
72 Posts

Posted - 12/02/2013 :  00:41:54  Show Profile  Reply with Quote
Thanks ,Ramakrishnan Sridharan sir
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000