give me your suggestion

Question

give me your suggestion

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
March 4, 2009 at 10:17 pm

#129542

I have a task like number(digits) to word format
example 1900 =thousand and nine hundred
if i using this type it takes very lengthy coding..
what i do
My attempt like this..
create PROCEDURE NUMTOWORD
@INPUT AS BIGINT
AS
BEGIN
IF(LEN(@INPUT) = 1)
SELECT CASE @INPUT
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 'ZERO'
ELSE '**' END
ELSE IF(LEN(@INPUT) = 2)
PRINT 'EXCEED'
END
GO

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

First, you should be warned that string manipulations like this should be done by the application, not in SQL. The reasons for this are to protect the SQL Server from the calculation load and to minimize the data being transported over the network. That said, if you MUST do it in SQL, the following code is an example of how it can be done.

If this is going to be a regular occurrence in your system, I would suggest using the above code to create a permanent table consisting of two columns. The first column would be a primary key consisting only of the integers for the range where you might use this. The second column would contain the full text of the number in words. You can then easily join to this table whenever you have to do the conversion. I suspect it will also run much faster, but I can't test that until later today. When I have a chance I will post up the code and the results.

Please let me know if you have any questions.

declare @sample int

set @sample = 1919

declare @numwords table(number int, ones varchar(15), tens varchar(15), hundreds varchar(15), thousands varchar(15))

insert into @numwords

select 0,'','','','' union all

select 1,'One','Ten','One Hundred','One Thousand' union all

select 2,'Two','Twenty','Two Hundred','Two Thousand' union all

select 3,'Three','Thirty','Three Hundred','Three Thousand' union all

select 4,'Four','Forty','Four Hundred','Four Thousand' union all

select 5,'Five','Fifty','Five Hundred','Five Thousand' union all

select 6,'Six','Sixty','Six Hundred','Six Thousand' union all

select 7,'Seven','Seventy','Seven Hundred','Seven Thousand' union all

select 8,'Eight','Eighty','Eight Hundred','Eight Thousand' union all

select 9,'Nine','Ninety','Nine Hundred','NineThousand' union all

select 10,'Nineteen','','','' union all

select 11,'Eleven','','','' union all

select 12,'Twelve','','','' union all

select 13,'Thirteen','','','' union all

select 14,'Fourteen','','','' union all

select 15,'Fifteen','','','' union all

select 16,'Sixteen','','','' union all

select 17,'Seventeen','','','' union all

select 18,'Eighteen','','','' union all

select 19,'Nineteen','','',''

select max(case when number = substring(reverse(@sample),4,1) then thousands else '' end)

,max(case when number = substring(reverse(@sample),3,1) then hundreds else '' end)

,max(case when right(@sample,2) not between 11 and 19 and number = substring(reverse(@sample),2,1) then tens else '' end)

,max(case when right(@sample,2) not between 11 and 19 and number = right(@sample,1) then ones

when right(@sample,2) between 11 and 19 and number = right(@sample,2) then ones

else '' end)

,max(right(@sample,2))/*and number = substring(reverse(@sample),2,1) <> 1 then ones

when substring(reverse(@sample),2,1) = 1 and number = right(@sample,2) then ones

else '' end) */

from @Numwords

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

noeld SSC Guru Points: 96590 More actions · Answer 2

http://users.drew.edu/skass/sql/NameMoney.sql.txt

* Noel

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Once the table is built it appears to be more efficient to just look a number up. Running the test below with 9999 different numbers, the join to the existing table takes about 200 ms elapsed time and the version which builds the string on the fly runs in over 1300 ms. The difference in CPU time is MUCH greater. However, there may be much faster ways to build the string than the solution I threw together. If I find one that runs faster than simply looking the string up by means of a join to a prepopulated table, I will post it here.

declare @numwords table(number int, ones varchar(15), tens varchar(15), hundreds varchar(15), thousands varchar(15))

insert into @numwords

select 0,NULL,NULL,NULL,NULL union all