Jump to content
Fórum Script Brasil
  • 0

Dica: Valores por Extenso (Ordinários, Romanos, Monetários)


Question

===============================================================================
MSSQLServer : Função que retorna "POR EXTENSO" um valor informado
===============================================================================

 

--DROP FUNCTION dbo.fnc_txt_extenso;
CREATE FUNCTION dbo.fnc_txt_extenso(@chrTipo CHAR(1), @numValor NUMERIC(32,2))
RETURNS VARCHAR(500)
AS
BEGIN  
  DECLARE @txtExtenso       VARCHAR(500)
        , @numFixValor      NUMERIC(30,0)
        , @vchFixValor      VARCHAR(50)
        , @chrMilValor      CHAR(3)
        , @vchMilValor      VARCHAR(20)
        , @vchCenValor      VARCHAR(20)
        , @vchDezValor      VARCHAR(20)
        , @vchUndValor      VARCHAR(20)
        , @intCenValor      INTEGER
        , @intDezValor      INTEGER
        , @intUndValor      INTEGER
        , @intQtdPartes     INTEGER
        , @intQtdFracao            INTEGER

  SET @intQtdFracao = CHARINDEX(UPPER(@chrTipo), 'EMOR')

  IF (@intQtdFracao = 0)
  BEGIN
    RETURN 'As opções para valores em extenso são "(E)xtenso, (M)onetário, (O)rdinários e (R)omanos'
  END

  -- Divisão entre Inteiros e decimais (??? * (10^27))...
  SET @intQtdPartes = 1
  SET @txtExtenso = ''
 
  WHILE (@intQtdPartes >= CASE WHEN (UPPER(@chrTipo) IN ('O','R')) THEN 1 ELSE 0 END)
  BEGIN

    IF (@intQtdPartes = 1)
    BEGIN
      -- Pegando a parte inteira do parâmetro...
      SET @numFixValor = ROUND(ABS(@numValor),0,1)
      SET @vchFixValor = CONVERT(VARCHAR(30), @numFixValor)
      -- Verificando se o número de digitos é na ordem de centenas.
      SET @intQtdFracao = CASE WHEN ((LEN(@vchFixValor)% 3) > 0) THEN (3 - (LEN(@vchFixValor)% 3)) ELSE 0 END
      -- Se o valor não estiver no formatado de centena....
      SET @vchFixValor = RIGHT(REPLICATE('0',LEN(@vchFixValor) + @intQtdFracao) + @vchFixValor ,LEN(@vchFixValor) + @intQtdFracao)
    END ELSE
    IF (@intQtdPartes = 0)
    BEGIN
     -- Pegando o valor de dois dígitos de casas decimais....
      SET @numFixValor = ROUND(((ROUND(ABS(@numValor),2,0) - ROUND(ABS(@numValor),0,1)) * 100),0,1)
      SET @vchFixValor = RIGHT(REPLICATE('0',3) + CONVERT(VARCHAR(6), @numFixValor),3)
      SET @intQtdFracao = 0
    END
    -- Pegando a quantidade de milhares de centenas....
    SET @intQtdFracao = ROUND((LEN(@vchFixValor)/3)-1,0)
    
    WHILE @intQtdFracao >= 0
    BEGIN
      SET @chrMilValor = SUBSTRING(@vchFixValor, (1 + LEN(@vchFixValor) - (3 * (@intQtdFracao + 1))), 3)
      SET @intCenValor = CONVERT(INT, SUBSTRING(@chrMilValor,1, 1))
      SET @intDezValor = CONVERT(INT, SUBSTRING(@chrMilValor,2, 1))
      SET @intUndValor = CONVERT(INT, SUBSTRING(@chrMilValor,3, 1))
       
      IF (@chrTipo IS NULL)
      BEGIN
        SET @vchMilValor = 'L'
        SET @vchCenValor = 'N'
        SET @vchDezValor = 'U'
        SET @vchUndValor = 'L'
      END ELSE
      IF (UPPER(@chrTipo) = 'O')
      BEGIN
        SET @vchMilValor = CASE @intQtdFracao WHEN 1 THEN 'milésimo '
                                              WHEN 2 THEN 'milhionésimo '
                                              WHEN 3 THEN 'bilhionésimo '
                                              WHEN 4 THEN 'trilhonésimo '
                                              WHEN 5 THEN 'quadrilhonésimo '
                                              WHEN 6 THEN 'quintilionésimo '
                                              WHEN 7 THEN 'sextilionésimo '
                                              WHEN 8 THEN 'septilionésimo '
                                              WHEN 9 THEN 'octilionésimo '
                                              ELSE '' END


        SET @vchCenValor = CASE @intCenValor  WHEN 1 THEN 'centésimo '
                                              WHEN 2 THEN 'ducentésimo '
                                              WHEN 3 THEN 'trecentésimo '
                                              WHEN 4 THEN 'quadringentésimo '
                                              WHEN 5 THEN 'qüingentésimo '
                                              WHEN 6 THEN 'sexcentésimo '
                                              WHEN 7 THEN 'septingentésimo '
                                              WHEN 8 THEN 'octingentésimo '
                                              WHEN 9 THEN 'noningentésimo '
                                              ELSE '' END

        SET @vchDezValor = CASE @intDezValor  WHEN 1 THEN 'décimo '
                                              WHEN 2 THEN 'vigésimo '
                                              WHEN 3 THEN 'trigêsimo '
                                              WHEN 4 THEN 'quadragésimo '
                                              WHEN 5 THEN 'qüinquagésimo '
                                              WHEN 6 THEN 'sexagésimo '
                                              WHEN 7 THEN 'septuagésimo '
                                              WHEN 8 THEN 'octoagésimo '
                                              WHEN 9 THEN 'nonagésimo '
                                              ELSE '' END

        SET @vchUndValor = CASE @intUndValor  WHEN 1 THEN 'primeiro '
                                              WHEN 2 THEN 'segundo '
                                              WHEN 3 THEN 'terceiro '
                                              WHEN 4 THEN 'quarto '
                                              WHEN 5 THEN 'quinto '
                                              WHEN 6 THEN 'sexto '
                                              WHEN 7 THEN 'sétimo '
                                              WHEN 8 THEN 'oitavo '
                                              WHEN 9 THEN 'nono '
                                              ELSE '' END
      END ELSE
      IF (UPPER(@chrTipo) = 'R')
      BEGIN

      
        SET @vchMilValor = REPLICATE('|', @intQtdFracao)

        SET @vchCenValor = CASE @intCenValor  WHEN 1 THEN 'C'
                                              WHEN 2 THEN 'CC'
                                              WHEN 3 THEN 'CCC'
                                              WHEN 4 THEN 'CD'
                                              WHEN 5 THEN 'D'
                                              WHEN 6 THEN 'DC'
                                              WHEN 7 THEN 'DCC'
                                              WHEN 8 THEN 'DCCC'
                                              WHEN 9 THEN 'CM'
                                              ELSE '' END

        SET @vchDezValor = CASE @intDezValor  WHEN 1 THEN 'X'
                                              WHEN 2 THEN 'XX'
                                              WHEN 3 THEN 'XXX'
                                              WHEN 4 THEN 'XL'
                                              WHEN 5 THEN 'L'
                                              WHEN 6 THEN 'LX'
                                              WHEN 7 THEN 'LXX'
                                              WHEN 8 THEN 'LXXX'
                                              WHEN 9 THEN 'XC'
                                              ELSE '' END

        SET @vchUndValor = CASE @intUndValor  WHEN 1 THEN 'I'
                                              WHEN 2 THEN 'II'
                                              WHEN 3 THEN 'III'
                                              WHEN 4 THEN 'IV'
                                              WHEN 5 THEN 'V'
                                              WHEN 6 THEN 'VI'
                                              WHEN 7 THEN 'VII'
                                              WHEN 8 THEN 'VIII'
                                              WHEN 9 THEN 'IX'
                                              ELSE '' END
 
        -- Tratamento da p...das excepções para algarismo de ...lhões...
        IF (@intQtdFracao > 0) AND (@intCenValor = 0) AND (@intDezValor = 0) AND (@intUndValor BETWEEN 1 AND 3)  
        BEGIN
          SET @vchUndValor = REPLACE(@vchUndValor, 'I', 'M')
          SET @vchMilValor = SUBSTRING(@vchMilValor, 2,LEN(@vchMilValor))
        END

      END ELSE
      IF (UPPER(@chrTipo) = 'E') OR (UPPER(@chrTipo) = 'M')
      BEGIN
        SET @vchMilValor = CASE @intQtdFracao WHEN 1 THEN 'mil '
                                              WHEN 2 THEN 'milhão '
                                              WHEN 3 THEN 'bilhão '
                                              WHEN 4 THEN 'trilhão '
                                              WHEN 5 THEN 'quadrilhão '
                                              WHEN 6 THEN 'quintilhão '
                                              WHEN 7 THEN 'sextilhão '
                                              WHEN 8 THEN 'septilhão '
                                              WHEN 9 THEN 'octilião '
                                              ELSE '' END

        SET @vchCenValor = CASE @intCenValor  WHEN 1 THEN 'cem '
                                              WHEN 2 THEN 'duzentos '
                                              WHEN 3 THEN 'trezentos '
                                              WHEN 4 THEN 'quatrocentos '
                                              WHEN 5 THEN 'quinhentos '
                                              WHEN 6 THEN 'seiscentos '
                                              WHEN 7 THEN 'setecentos '
                                              WHEN 8 THEN 'oitocentos '
                                              WHEN 9 THEN 'novecentos '
                                              ELSE '' END

        SET @vchDezValor = CASE @intDezValor  WHEN 1 THEN (
                           CASE @intUndValor  WHEN 0 THEN 'dez '
                                              WHEN 1 THEN 'onze '
                                              WHEN 2 THEN 'doze '
                                              WHEN 3 THEN 'treze '
                                              WHEN 4 THEN 'quartoze '
                                              WHEN 5 THEN 'quinze '
                                              WHEN 6 THEN 'dezesseis '
                                              WHEN 7 THEN 'dezesete '
                                              WHEN 8 THEN 'dezoito '
                                              WHEN 9 THEN 'dezenove '
                                              ELSE '' END) -- fine dell'eccezione.
                                              WHEN 2 THEN 'vinte '
                                              WHEN 3 THEN 'trinta '
                                              WHEN 4 THEN 'quarenta '
                                              WHEN 5 THEN 'cinqüenta '
                                              WHEN 6 THEN 'sessenta '
                                              WHEN 7 THEN 'setenta '
                                              WHEN 8 THEN 'oitenta '
                                              WHEN 9 THEN 'noventa '
                                              ELSE '' END

        SET @vchUndValor = CASE @intUndValor  WHEN 1 THEN 'um '
                                              WHEN 2 THEN 'dois '
                                              WHEN 3 THEN 'três '
                                              WHEN 4 THEN 'quatro '
                                              WHEN 5 THEN 'cinco '
                                              WHEN 6 THEN 'seis '
                                              WHEN 7 THEN 'sete '
                                              WHEN 8 THEN 'oito '
                                              WHEN 9 THEN 'nove '      
                                              ELSE '' END

        -- Tratamento das excepções para valores de dez...
        IF (@intDezValor = 1)
          SET @vchUndValor = ''

        -- Tratamento das excepções para valores de cen...
        IF (@intCenValor = 1) AND ((@intDezValor > 0) OR (@intUndValor > 0))
         SET @vchCenValor = 'cento '

        -- Tratamento das excepções para valores de ...lhões...
        IF (@intQtdFracao > 0) AND ((@intCenValor > 0) OR (@intDezValor > 0) OR (@intUndValor > 1))
          SET @vchMilValor = REPLACE(@vchMilValor, 'ão', 'ões')
        
        -- Tratamento da excepção referente a milhar interligada...
        IF (@intQtdFracao > 0) AND ((@intCenValor > 0) OR (@intDezValor > 0) OR (@intUndValor > 0)) AND
           (CONVERT(FLOAT, SUBSTRING(@vchFixValor, (1 + LEN(@vchFixValor) - (3 * @intQtdFracao)), LEN(@vchFixValor))) > 0)
          SET @vchMilValor = @vchMilValor + ' e '
        
        -- Tratamento da excepção referente a centena interligada...
        IF (@intCenValor > 0) AND ((@intDezValor > 0) OR (@intUndValor > 0))
          SET @vchCenValor = @vchCenValor + ' e '
        
        -- Tratamento da excepção referente a dezena interligada...
        IF (@intDezValor > 1) AND (@intUndValor > 0)
          SET @vchDezValor = @vchDezValor + ' e '
       
      END
      -- Tratamento das excepções referente a unidade de milhão...
      IF (UPPER(@chrTipo) <> 'R') AND (@intQtdFracao = 1) AND (@intCenValor = 0) AND (@intDezValor = 0) AND (@intUndValor = 1)
        SET @vchUndValor = ''

      -- Tratamento das excepções referente a unidade de milhão...
      IF (UPPER(@chrTipo) = 'O') AND (@intQtdFracao > 1) AND (@intCenValor = 0) AND (@intDezValor = 0) AND (@intUndValor = 1)
        SET @vchUndValor = ''
      
      -- Tratamento da das excepções referente a casa de milhares...
      IF (@intQtdFracao > 0) AND (@intCenValor = 0) AND (@intDezValor = 0) AND (@intUndValor = 0)
        SET @vchMilValor = ''
      
      -- Se a parte decimais do valor for informado...
      IF (@intQtdPartes = 0) AND (@numFixValor > 0)
      BEGIN  
        IF ((UPPER(@chrTipo) = 'E') OR (UPPER(@chrTipo) = 'M')) AND (@txtExtenso > '')
          SET @txtExtenso = RTRIM(@txtExtenso) +  ', e '
        ELSE
        IF (UPPER(@chrTipo) = 'E') AND (@txtExtenso = '')
          SET @txtExtenso = RTRIM(@txtExtenso) +  'zero, '    
        ELSE
        IF (@txtExtenso > '')
          SET @txtExtenso = RTRIM(@txtExtenso) +  ', '    
      END

      -- Concatena todas as expressões milhares, centenas, dezenas e unidades...
      SET @txtExtenso = @txtExtenso + @vchCenValor + @vchDezValor + @vchUndValor + @vchMilValor
      -- Retorno laço de frações...
      SET @intQtdFracao = @intQtdFracao - 1
    END

    -- Se o valor informado for do tipo extenso...
    IF (UPPER(@chrTipo) = 'E') AND (@intQtdPartes = 0)
    BEGIN
       IF (@intDezValor = 0) AND (@intUndValor = 1)
         SET @txtExtenso = @txtExtenso + 'décimo'
       ELSE
       IF (@numFixValor > 1)
         SET @txtExtenso = @txtExtenso + 'décimos'
    END
    -- Se o valor informado for do tipo moeda...
    IF (UPPER(@chrTipo) = 'M') AND (@numFixValor > 0)
    BEGIN
      -- Se for na casa de "...lhões"
      IF (@intQtdPartes = 1) AND (PATINDEX('%' + SUBSTRING(RTRIM(@txtExtenso),LEN(RTRIM(@txtExtenso))-2,3) + '%','õeshãoião') > 0)
        SET  @txtExtenso = @txtExtenso + 'de '
      -- Informar no valor a moeda corrente...
      IF (@intQtdPartes = 1) AND (@intCenValor = 0) AND (@intDezValor = 0) AND (@intUndValor = 1)
         SET @txtExtenso = @txtExtenso + 'real '
      ELSE
      IF (@intQtdPartes = 1)
         SET @txtExtenso = @txtExtenso + 'reais '
      ELSE
      IF (@intQtdPartes = 0) AND (@intDezValor = 0) AND (@intUndValor = 1)
        SET @txtExtenso = @txtExtenso + 'centavo '   
      ELSE
      IF (@intQtdPartes = 0)
        SET @txtExtenso = @txtExtenso + 'centavos '   
    END
    -- Retrono do laço das partes (inteiro e fracionário)do número informado...
    SET @intQtdPartes = @intQtdPartes - 1  
  END

  RETURN LTRIM(RTRIM(REPLACE(@txtExtenso,'  ',' ')))
  -- ===================( Exemple Function Return Extensive )====================
  --  SELECT dbo.fnc_txt_extenso('E',1234567890123456789012.34) AS "Value Extensive";
  --  SELECT dbo.fnc_txt_extenso('O',1234); {Developer by Jair Bridi Gozze}
  -- ============================================================================
END
GO

---------------------------------------------------------------------------------
Exemplo:
---------------------------------------------------------------------------------
--OBS Veja a indicação do parâmetro que pode ser EMOR na declaração


     SELECT (seq * sup) AS "Números Arábicos" -- Apresentação de 1...100 intervalo de 1;
          , [dbo].[fnc_txt_extenso]('R', seq * sup) AS "Números Romanos"
          , [dbo].[fnc_txt_extenso]('O', seq * sup) AS "Extenso Ordinal"
          , [dbo].[fnc_txt_extenso]('E', seq * sup) AS "Numeros Extenso"
          , [dbo].[fnc_txt_extenso]('M', seq * sup) AS "Valor Monetário"
       FROM (SELECT (1 + ones.n + (10 * tens.n)) AS seq, 1000 AS sup
               FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
           )  sequencial;

 

Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Cloud Computing


  • Forum Statistics

    • Total Topics
      148720
    • Total Posts
      644545
×
×
  • Create New...