Loading ...
Sorry, an error occurred while loading the content.

RE: [mugh-sqlcon] Help String Conversion

Expand Messages
  • aneesh
    Thanks Chevendra ... dbo.udf_Num_ToWords(76543210987654321098765432109876543210) ... === message truncated === __________________________________ Do you
    Message 1 of 2 , Apr 29, 2004
    • 0 Attachment
      Thanks Chevendra

      --- "Viswamitra Chevendra (Wipro Ltd.)"
      <v-vische@...> wrote:
      > By this time you would have searched on net and got
      > the answer. If not
      > here is one (among many)
      >
      >
      >
      > SET QUOTED_IDENTIFIER ON
      >
      > SET ANSI_NULLS ON
      >
      > SET NOCOUNT ON
      >
      > GO
      >
      >
      >
      >
      >
      >
      >
      > CREATE FUNCTION dbo.udf_Num_ToWords (
      >
      >
      >
      > @Number Numeric (38, 0) -- Input number
      > with as many as 18
      > digits
      >
      >
      >
      > ) RETURNS VARCHAR(8000)
      >
      > /*
      >
      > * Converts a integer number as large as 34 digits
      > into the
      >
      > * equivalent words. The first letter is
      > capitalized.
      >
      > *
      >
      > * Attribution: Based on NumberToWords by Srinivas
      > Sampath
      >
      > * as revised by Nick Barclay
      >
      > *
      >
      > * Example:
      >
      > select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      >
      > + dbo.udf_Num_ToWords (0) + CHAR(10)
      >
      > + dbo.udf_Num_ToWords (123) + CHAR(10)
      >
      > select
      >
      dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
      >
      >
      >
      > DECLARE @i numeric (38,0)
      >
      > SET @i = 0
      >
      > WHILE @I <= 1000 BEGIN
      >
      > PRINT convert (char(5), @i)
      >
      > + convert(varchar(255),
      > dbo.udf_Num_ToWords(@i))
      >
      > SET @I = @i + 1
      >
      > END
      >
      > *
      >
      > * Published as the T-SQL UDF of the Week Vol 2 #9
      > 2/17/03
      >
      >
      ****************************************************************/
      >
      > AS BEGIN
      >
      >
      >
      > DECLARE @inputNumber VARCHAR(38)
      >
      > DECLARE @NumbersTable TABLE (number CHAR(2), word
      > VARCHAR(10))
      >
      > DECLARE @outputString VARCHAR(8000)
      >
      > DECLARE @length INT
      >
      > DECLARE @counter INT
      >
      > DECLARE @loops INT
      >
      > DECLARE @position INT
      >
      > DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
      >
      > DECLARE @tensones CHAR(2)
      >
      > DECLARE @hundreds CHAR(1)
      >
      > DECLARE @tens CHAR(1)
      >
      > DECLARE @ones CHAR(1)
      >
      >
      >
      > IF @Number = 0 Return 'Zero'
      >
      >
      >
      > -- initialize the variables
      >
      > SELECT @inputNumber = CONVERT(varchar(38), @Number)
      >
      > , @outputString = ''
      >
      > , @counter = 1
      >
      > SELECT @length = LEN(@inputNumber)
      >
      > , @position = LEN(@inputNumber) - 2
      >
      > , @loops = LEN(@inputNumber)/3
      >
      >
      >
      > -- make sure there is an extra loop added for the
      > remaining numbers
      >
      > IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops +
      > 1
      >
      >
      >
      > -- insert data for the numbers and words
      >
      > INSERT INTO @NumbersTable SELECT '00', ''
      >
      > UNION ALL SELECT '01', 'one' UNION ALL
      > SELECT '02', 'two'
      >
      > UNION ALL SELECT '03', 'three' UNION ALL
      > SELECT '04', 'four'
      >
      > UNION ALL SELECT '05', 'five' UNION ALL
      > SELECT '06', 'six'
      >
      > UNION ALL SELECT '07', 'seven' UNION ALL
      > SELECT '08', 'eight'
      >
      > UNION ALL SELECT '09', 'nine' UNION ALL
      > SELECT '10', 'ten'
      >
      > 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' UNION ALL
      > SELECT '20', 'twenty'
      >
      > UNION ALL SELECT '30', 'thirty' UNION ALL
      > SELECT '40', 'forty'
      >
      > UNION ALL SELECT '50', 'fifty' UNION ALL
      > SELECT '60', 'sixty'
      >
      > UNION ALL SELECT '70', 'seventy' UNION ALL
      > SELECT '80', 'eighty'
      >
      > UNION ALL SELECT '90', 'ninety'
      >
      >
      >
      > WHILE @counter <= @loops BEGIN
      >
      >
      >
      > -- get chunks of 3 numbers at a time,
      > padded with leading
      > zeros
      >
      > SET @chunk = RIGHT('000' +
      > SUBSTRING(@inputNumber,
      > @position, 3), 3)
      >
      === message truncated ===


      __________________________________
      Do you Yahoo!?
      Yahoo! SiteBuilder - Free web site building tool. Try it!
      http://webhosting.yahoo.com/ps/sb/
    Your message has been successfully submitted and would be delivered to recipients shortly.