Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 1st, 2006, 04:33 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default converting numbers to words

if i give number either in Rs or in dollars distinguishing by commas(,) with no limt to the number how to convert into words as normal as we spell the number.. plz help me in writing procedure. i am using sql server 2000. expecting your friendly hands..

---
ali
Reply With Quote
  #2 (permalink)  
Old March 1st, 2006, 12:31 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Is this what you want to do?

Convert to this.
1,300 One Thousand Three Hundred

Reply With Quote
  #3 (permalink)  
Old March 2nd, 2006, 02:02 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ofcourse its a part as if i give 123405.20$ it should display as
One hundred and twenty-three thousand four hundred five dollars and twenty cents. if i give same as 123405.20 Rs it should display as one lakh twenty three thousand four hundred and five rupees twenty paise..
----
ali
Reply With Quote
  #4 (permalink)  
Old March 2nd, 2006, 12:34 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

I don't know of any function in SQL that does that. I suppose you could use a lookup table, split the string into its different parts, use that to look up the translation.

Do you know how to split a string in SQL? If not look up LEN, SUBSTRING, CHARINDEX, RIGHT and LEFT in the BOL. I haven't actually tried this specific scenario but it just might work.

You'll have to create a table with the tranlation from 1 - 100.

Number Translation
1 one
2 two
3 three
etc.

Split up the number, query the table, concatenate with the words 'thousand', 'hundred', etc., capitalize the first letter and print it.

Here's a start. Its not great but it will give you the general idea.

declare @thousands varchar(3), @hundredthousands varchar(25), @hundreds varchar(3), @cents varchar(2), @theValue varchar(20), @theTotalLength int, @theFinalString varchar(200)

Set @theValue = '123405.20'
Set @theTotalLength = Len(@thevalue)

IF LEN(@theValue) > 6
SET @thousands = REVERSE(SUBSTRING(REVERSE(@theValue),7,3))

If LEN(@thousands) > 2
Set @hundredthousands = LEFT(@thousands,1)


IF LEN(@hundredthousands) > 0
    SET @hundredthousands = (select trans from trans where numbers = @hundredthousands)
IF LEN(@hundredthousands) > 0
    select @hundredthousands + ' hundred'



Reply With Quote
  #5 (permalink)  
Old March 3rd, 2006, 02:10 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for suggestion and i will try ..
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting numbers to text mylifeishz Visual Basic 2005 Basics 1 July 5th, 2008 10:57 AM
Convert Numbers to Words hasankelepir Classic ASP Basics 5 October 1st, 2007 07:15 AM
Numbers to words and vice versa? code_lover SQL Language 0 January 7th, 2007 04:50 PM
Converting military times to numbers Wire323 Excel VBA 1 April 6th, 2006 01:47 AM
generating words kozam C++ Programming 1 September 30th, 2003 12:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.