come scrivere la funzione da numero a parola in sql server

Come si scrive una funzione in SQL Server per produrre un numero in parole?

input: 1
uscita: uno

input: 129
produzione: centoventinove

Prendi in considerazione l’utilizzo di una tabella di numeri ausiliari.

NB: questo MS SQL

Crea una tabella di sequenza – Questo potrebbe includere tutti i numeri necessari o almeno fino a 999. L’ho limitato al minimo, ma aggiunge logica aggiuntiva.

CREATE TABLE [dbo].[Sequence] ( seq INTEGER NOT NULL UNIQUE, word [varchar](25) NOT NULL ) INSERT INTO [Sequence] SELECT 0, '' INSERT INTO [Sequence] SELECT 1, 'One' INSERT INTO [Sequence] SELECT 2, 'Two' INSERT INTO [Sequence] SELECT 3, 'Three' INSERT INTO [Sequence] SELECT 4, 'Four' INSERT INTO [Sequence] SELECT 5, 'Five' INSERT INTO [Sequence] SELECT 6, 'Six' INSERT INTO [Sequence] SELECT 7, 'Seven' INSERT INTO [Sequence] SELECT 8, 'Eight' INSERT INTO [Sequence] SELECT 9, 'Nine' INSERT INTO [Sequence] SELECT 10, 'Ten' INSERT INTO [Sequence] SELECT 11, 'Eleven' INSERT INTO [Sequence] SELECT 12, 'Twelve' INSERT INTO [Sequence] SELECT 13, 'Thirteen' INSERT INTO [Sequence] SELECT 14, 'Fourteen' INSERT INTO [Sequence] SELECT 15, 'Fifteen' INSERT INTO [Sequence] SELECT 16, 'Sixteen' INSERT INTO [Sequence] SELECT 17, 'Seventeen' INSERT INTO [Sequence] SELECT 18, 'Eighteen' INSERT INTO [Sequence] SELECT 19, 'Nineteen' INSERT INTO [Sequence] SELECT 20, 'Twenty' INSERT INTO [Sequence] SELECT 30, 'Thirty' INSERT INTO [Sequence] SELECT 40, 'Forty' INSERT INTO [Sequence] SELECT 50, 'Fifty' INSERT INTO [Sequence] SELECT 60, 'Sixty' INSERT INTO [Sequence] SELECT 70, 'Seventy' INSERT INTO [Sequence] SELECT 80, 'Eighty' INSERT INTO [Sequence] SELECT 90, 'Ninty' 

Quindi creare la funzione definita dall’utente.

 CREATE FUNCTION dbo.udf_NumToWords ( @num AS INTEGER ) RETURNS VARCHAR(50) AS BEGIN DECLARE @words AS VARCHAR(50) IF @num = 0 SELECT @words = 'Zero' ELSE IF @num < 20 SELECT @words = word FROM sequence WHERE seq = @num ELSE IF @num < 100 (SELECT @words = TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 ) ELSE IF @num = 100 (SELECT @words = THundreds.word + ' Hundred' FROM Sequence AS THundreds WHERE THundreds.seq = (@num / 100) ) ELSE IF @num < 1000 ( SELECT @words = THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 AND THundreds.seq = (@num / 100) ) ELSE IF @num = 1000 (SELECT @words = TThousand.word + ' Thousand' FROM Sequence AS TThousand WHERE TThousand.seq = (@num / 1000) ) ELSE IF @num < 10000 ( SELECT @words = TThousand.word + ' Thousand ' + THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds CROSS JOIN Sequence AS TThousand WHERE TUnits.seq = (@num % 100) % 10 AND TTens.seq = (@num % 100) - (@num % 100) % 10 AND THundreds.seq = (@num / 100) - (@num / 1000) * 10 AND TThousand.seq = (@num / 1000) ) ELSE SELECT @words = STR(@num) RETURN @words END 

Ora funzione di test:

 SELECT NumberAsWords = dbo.udf_NumToWords(888); 

Questa è una leggera modifica della risposta di cui sopra da parte di Andrew (tutti i crediti per Andrew), ma ciò restituirà risultati nella query sql senza utilizzare alcuna funzione

  WITH Sequence AS ( SELECT 0 seq ,'' word UNION ALL SELECT 1 ,'One' UNION ALL SELECT 2 ,'Two' UNION ALL SELECT 3 ,'Three' UNION ALL SELECT 4 ,'Four' UNION ALL SELECT 5 ,'Five' UNION ALL SELECT 6 ,'Six' UNION ALL SELECT 7 ,'Seven' UNION ALL SELECT 8 ,'Eight' UNION ALL SELECT 9 ,'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 ,'Ninty' ) ,CTENumbers AS ( SELECT 0 AS num --changezero to your starting number UNION ALL SELECT num + 1 FROM CTENumbers WHERE num + 1 < = 255 --change 255 to upper limit ) SELECT * ,CASE WHEN num = 0 THEN 'Zero' WHEN num < 20 THEN ( SELECT word FROM sequence WHERE seq = num ) WHEN num < 100 THEN ( SELECT TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens WHERE TUnits.seq = (num % 100) % 10 AND TTens.seq = (num % 100) - (num % 100) % 10 ) WHEN num < 1000 THEN ( SELECT THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds WHERE TUnits.seq = (num % 100) % 10 AND TTens.seq = (num % 100) - (num % 100) % 10 AND THundreds.seq = (num / 100) ) WHEN num = 1000 THEN ( SELECT TThousand.word + ' Thousand' FROM Sequence AS TThousand WHERE TThousand.seq = (num / 1000) ) WHEN num < 10000 THEN ( SELECT TThousand.word + ' Thousand ' + THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word FROM Sequence AS TUnits CROSS JOIN Sequence AS TTens CROSS JOIN Sequence AS THundreds CROSS JOIN Sequence AS TThousand WHERE TUnits.seq = (num % 100) % 10 AND TTens.seq = (num % 100) - (num % 100) % 10 AND THundreds.seq = (num / 100) - (num / 1000) * 10 AND TThousand.seq = (num / 1000) ) ELSE STR(num) END Number FROM CTENumbers OPTION (MAXRECURSION 10000) 

Solo se aiuta qualcuno potresti creare una tabella di numeri e riempirla usando un linguaggio lato server come c #. Puoi anche usare la libreria di Humanizer per convertire il numero in parole; supporta anche le localizzazioni.

TABELLA SQL

 CREATE TABLE [Numbers]( [n] [bigint] NOT NULL, [InWords] [nvarchar](50) NULL, ) 

Codice C #

Richiede l’ umanista

 var db = new MyDbContext(); for (int i = 1; i < 1000; i++) { db.Database.ExecuteSqlCommand($"INSERT INTO Numbers (n, InWords) VALUES({i}, '{i.ToWords()}')"); }