domingo, 11 de junio de 2017

FUNCIÓN POSTGRESQL PARA CONVERTIR NÚMEROS 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;