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.
Author |
Topic |
rajnidas
Yak Posting Veteran
97 Posts |
Posted - 2013-11-29 : 07:29:04
|
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 EndEND 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 Paise2.select dbo.fn_spellnumber('12,34,56,789.35','L',1,1)result:Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundr3.select dbo.fn_spellnumber('12,34,56,789.35','L',1,2)result: ed And Eighty Nine And Thirty Five Paisei 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
8 Posts |
Posted - 2013-11-30 : 00:51:39
|
--------------------------------------------------------------------------------------------------------------------------------------------------------- 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] |
|
|
ijmar86
Starting Member
8 Posts |
Posted - 2013-11-30 : 00:57:56
|
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 MASTERIF OBJECT_ID('dbo.fnSpellInteger') IS NOT NULL DROP FUNCTION fnSpellIntegerGOCREATE FUNCTION dbo.fnSpellInteger ( @number int )RETURNS VARCHAR(100)ASBEGIN -- 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)ENDGOSET 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 MASTERIF OBJECT_ID('master.dbo.fnSpellNumber') IS NOT NULL DROP FUNCTION fnSpellNumberGOCREATE 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 @resultENDGO |
|
|
rajnidas
Yak Posting Veteran
97 Posts |
Posted - 2013-12-02 : 00:41:54
|
Thanks ,Ramakrishnan Sridharan sir |
|
|
|
|
|
|
|