cómo escribir la función de número a palabra en el servidor SQL

¿Cómo se podría escribir una función en SQL Server para generar un número en palabras?

entrada: 1
salida: uno

entrada: 129
salida: ciento veintinueve

Considere usar una tabla de números auxiliares.

NB: Este MS SQL

Crear una tabla de secuencias: podría incluir todos los números que necesite o, al menos, hasta 999. Lo he limitado al mínimo, pero agrega una lógica extra.

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' 

A continuación, crea la función definida por el usuario.

 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 

Ahora la función de prueba:

 SELECT NumberAsWords = dbo.udf_NumToWords(888); 

Esta es una pequeña modificación de la respuesta anterior de Andrew (todos los créditos para Andrew) pero esto devolverá los resultados en la consulta sql sin usar ninguna función

  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 si ayuda a alguien, podría crear una tabla de números y llenarla con un lenguaje del lado del servidor como c #. También puede usar la biblioteca de Humanizer para convertir el número en palabras; también admite localizaciones.

TABLA SQL

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

C # Code

Requiere Humanizer

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