Función Postgres para convertir número a palabras
CREATE OR REPLACE FUNCTION fn_num_a_palabra(num BIGINT) RETURNS TEXT AS
$$
DECLARE
palab TEXT;
BEGIN
WITH menor20(palabra, id) AS
(
VALUES
('CERO', 0), ('UNO', 1),( 'DOS', 2 ), ( 'TRES', 3),
( 'CUATRO', 4 ), ( 'CINCO', 5 ), ( 'SEIS', 6 ), ( 'SIETE', 7 ),
( 'OCHO', 8), ( 'NUEVE', 9), ( 'DIEZ', 10), ( 'ONCE', 11 ),
( 'DOCE', 12 ), ( 'TRECE', 13 ), ( 'CATORCE', 14),
( 'QUINCE', 15 ), ('DIECISEIS', 16 ), ( 'DIECISIETE', 17),
('DIECIOCHO', 18 ), ( 'DIECINUEVE', 19 )
),
menor100(palabra, id) AS
(
VALUES
('VEINTE', 2), ('TREINTA', 3),('CUARENTA', 4), ('CINCUENTA', 5),
('SESENTA', 6), ('SETENTA', 7), ('OCHENTA', 8), ('NOVENTA', 9)
)
SELECT
CASE
WHEN num = 0 THEN ''
WHEN num BETWEEN 1 AND 19
THEN (SELECT palabra FROM menor20 WHERE id=num)
WHEN num BETWEEN 20 AND 99
THEN (SELECT palabra FROM menor100 WHERE id=num/10) || ' Y ' ||
fn_num_a_palabra( num % 10)
WHEN num = 100
THEN 'CIEN'
WHEN num BETWEEN 101 AND 199
THEN 'CIENTO ' ||
fn_num_a_palabra( num % 100)
WHEN num BETWEEN 200 AND 999
THEN (fn_num_a_palabra( num / 100)) || ' CIENTOS ' ||
fn_num_a_palabra( num % 100)
WHEN num = 1000
THEN 'MIL'
WHEN num BETWEEN 1001 AND 1999
THEN 'MIL ' ||
fn_num_a_palabra( num % 1000)
WHEN num BETWEEN 2000 AND 999999
THEN (fn_num_a_palabra( num / 1000)) || ' MIL ' ||
fn_num_a_palabra( num % 1000)
WHEN num BETWEEN 1000000 AND 999999999
THEN (fn_num_a_palabra( num / 1000000)) || ' MILLONES ' ||
fn_num_a_palabra( num % 1000000)
WHEN num BETWEEN 1000000000 AND 999999999999
THEN (fn_num_a_palabra( num / 1000000000)) || ' BILLONES ' ||
fn_num_a_palabra( num % 1000000000)
WHEN num BETWEEN 1000000000000 AND 999999999999999
THEN (fn_num_a_palabra( num / 1000000000000)) || ' TRILLONES ' ||
fn_num_a_palabra( num % 1000000000000)
WHEN num BETWEEN 1000000000000000 AND 999999999999999999
THEN (fn_num_a_palabra( num / 1000000000000000)) || ' CUATRILLONES ' ||
fn_num_a_palabra( num % 1000000000000000)
WHEN num BETWEEN 1000000000000000000 AND 999999999999999999999
THEN (fn_num_a_palabra( num / 1000000000000000000)) || ' QUINTILLONES ' ||
fn_num_a_palabra( num % 1000000000000000000)
ELSE ' ENTRADA NO VALIDA' END INTO palab;
palab := RTRIM(palab);
IF RIGHT(palab,1)='Y' THEN
palab := RTRIM(LEFT(palab,length(palab)-1));
END IF;
RETURN palab;
END;
$$ LANGUAGE PLPGSQL;
$$
DECLARE
palab TEXT;
BEGIN
WITH menor20(palabra, id) AS
(
VALUES
('CERO', 0), ('UNO', 1),( 'DOS', 2 ), ( 'TRES', 3),
( 'CUATRO', 4 ), ( 'CINCO', 5 ), ( 'SEIS', 6 ), ( 'SIETE', 7 ),
( 'OCHO', 8), ( 'NUEVE', 9), ( 'DIEZ', 10), ( 'ONCE', 11 ),
( 'DOCE', 12 ), ( 'TRECE', 13 ), ( 'CATORCE', 14),
( 'QUINCE', 15 ), ('DIECISEIS', 16 ), ( 'DIECISIETE', 17),
('DIECIOCHO', 18 ), ( 'DIECINUEVE', 19 )
),
menor100(palabra, id) AS
(
VALUES
('VEINTE', 2), ('TREINTA', 3),('CUARENTA', 4), ('CINCUENTA', 5),
('SESENTA', 6), ('SETENTA', 7), ('OCHENTA', 8), ('NOVENTA', 9)
)
SELECT
CASE
WHEN num = 0 THEN ''
WHEN num BETWEEN 1 AND 19
THEN (SELECT palabra FROM menor20 WHERE id=num)
WHEN num BETWEEN 20 AND 99
THEN (SELECT palabra FROM menor100 WHERE id=num/10) || ' Y ' ||
fn_num_a_palabra( num % 10)
WHEN num = 100
THEN 'CIEN'
WHEN num BETWEEN 101 AND 199
THEN 'CIENTO ' ||
fn_num_a_palabra( num % 100)
WHEN num BETWEEN 200 AND 999
THEN (fn_num_a_palabra( num / 100)) || ' CIENTOS ' ||
fn_num_a_palabra( num % 100)
WHEN num = 1000
THEN 'MIL'
WHEN num BETWEEN 1001 AND 1999
THEN 'MIL ' ||
fn_num_a_palabra( num % 1000)
WHEN num BETWEEN 2000 AND 999999
THEN (fn_num_a_palabra( num / 1000)) || ' MIL ' ||
fn_num_a_palabra( num % 1000)
WHEN num BETWEEN 1000000 AND 999999999
THEN (fn_num_a_palabra( num / 1000000)) || ' MILLONES ' ||
fn_num_a_palabra( num % 1000000)
WHEN num BETWEEN 1000000000 AND 999999999999
THEN (fn_num_a_palabra( num / 1000000000)) || ' BILLONES ' ||
fn_num_a_palabra( num % 1000000000)
WHEN num BETWEEN 1000000000000 AND 999999999999999
THEN (fn_num_a_palabra( num / 1000000000000)) || ' TRILLONES ' ||
fn_num_a_palabra( num % 1000000000000)
WHEN num BETWEEN 1000000000000000 AND 999999999999999999
THEN (fn_num_a_palabra( num / 1000000000000000)) || ' CUATRILLONES ' ||
fn_num_a_palabra( num % 1000000000000000)
WHEN num BETWEEN 1000000000000000000 AND 999999999999999999999
THEN (fn_num_a_palabra( num / 1000000000000000000)) || ' QUINTILLONES ' ||
fn_num_a_palabra( num % 1000000000000000000)
ELSE ' ENTRADA NO VALIDA' END INTO palab;
palab := RTRIM(palab);
IF RIGHT(palab,1)='Y' THEN
palab := RTRIM(LEFT(palab,length(palab)-1));
END IF;
RETURN palab;
END;
$$ LANGUAGE PLPGSQL;
Comentarios
Publicar un comentario