Funzione per tradurre da numeri a lettere

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(20))
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', 'uno'    UNION ALL SELECT '02', 'due'
    UNION ALL SELECT '03', 'tre'    UNION ALL SELECT '04', 'quattro'
    UNION ALL SELECT '05', 'cinque'     UNION ALL SELECT '06', 'sei'
    UNION ALL SELECT '07', 'sette'    UNION ALL SELECT '08', 'otto'
    UNION ALL SELECT '09', 'nove'    UNION ALL SELECT '10', 'dieci'
    UNION ALL SELECT '11', 'undici'    UNION ALL SELECT '12', 'dodici'
    UNION ALL SELECT '13', 'tredici'  UNION ALL SELECT '14', 'quattordici'
    UNION ALL SELECT '15', 'quindici'  UNION ALL SELECT '16', 'sedici'
    UNION ALL SELECT '17', 'seventeen'  UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'diciannove' UNION ALL SELECT '20', 'venti'
    UNION ALL SELECT '30', 'trenta'    UNION ALL SELECT '40', 'quaranta'
    UNION ALL SELECT '50', 'cinquanta'  UNION ALL SELECT '60', 'sessanta'
    UNION ALL SELECT '70', 'settanta'  UNION ALL SELECT '80', 'ottanta'
    UNION ALL SELECT '90', 'novanata'   

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)

  IF @chunk <> '000' BEGIN
    SELECT @tensones = SUBSTRING(@chunk, 2, 2)
         , @hundreds = SUBSTRING(@chunk, 1, 1)
         , @tens = SUBSTRING(@chunk, 2, 1)
         , @ones = SUBSTRING(@chunk, 3, 1)

    -- If twenty or less, use the word directly from @NumbersTable
    IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
      SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' mila ' WHEN 3 THEN ' milioni '
                       WHEN 4 THEN ' billioni '  WHEN 5 THEN ' trillioni '
                       WHEN 6 THEN ' quadrilioni ' WHEN 7 THEN ' quintilioni '
                       WHEN 8 THEN ' sestilioni '  WHEN 9 THEN ' settilioni '
                       WHEN 10 THEN ' ottilioni '  WHEN 11 THEN ' nonilioni '
                       WHEN 12 THEN ' decillioni '  WHEN 13 THEN ' undicilioni '
                       ELSE '' END
                               + @outputString
        END
     ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
                   + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' mila ' WHEN 3 THEN ' milione '
                       WHEN 4 THEN ' bilione '  WHEN 5 THEN ' trillione '
                       WHEN 6 THEN ' quadrilione ' WHEN 7 THEN ' quintilione '
                       WHEN 8 THEN ' sestilione '  WHEN 9 THEN ' settilione '
                       WHEN 10 THEN ' ottilione '  WHEN 11 THEN ' nonilione '
                       WHEN 12 THEN ' decillione '   WHEN 13 THEN ' undicilione '
                       ELSE '' END
                            + @outputString
    END

    -- now get the hundreds
    IF @hundreds <> '0' BEGIN
      SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
                      + ' cento ' 
                                + @outputString
    END
  END

  SELECT @counter = @counter + 1
       , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END
GO

GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC

GO


select dbo.udf_Num_ToWords (1000) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
      + dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
 GO

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra