Converter um texto em NUMERIC(18,4) ou NUMERIC(18,2)

Uma das coisas mais básicas que encontramos na programação é a conversão de texto para número. Isto muitas vezes ocorre porque extraímos o valor de que precisamos de um texto, mas enquanto o valor ainda for do tipo texto não poderemos fazer operações matemáticas como somar ou subtrair. Para resolver o problema é preciso fazer a conversão.

A SQL Function abaixo resolve este problema, ela possui dois parametros de entrada:

String com o valor: Por exemplo ‘R$ 1.123,4567’
Valor assumido caso a conversão falhe: Assume zero, mas você pode trocar o valor.

E retorna NUMERIC(18,4), mas eu sei que muitos de vocês preferem valores com 2 casas decimais, portanto onde vê NUMERIC(18,4) no código psql basta trocar por NUMERIC(18,2):

create or alter function STR_TO_NUMERIC4 (
    P_VALUE_AS_TEXT varchar(30),
    P_VALUE_DEFAULT_IF_FAILS numeric(18,4) = 0)
returns numeric(18,4)
as
BEGIN
  /* essa procedure/função retorna um numeric(18, 4) a partir de um tipo
     texto exibido como numero, por exemplo, '1.234,5678'
   O resultado será o numeric(18,4) 1234.5678 já no formato SQL decimal
   O segundo parametro é o valor que deverá ser retornado caso o texto númerico
     não seja válido, ex:
   a=STR_TO_NUMERIC4('R$ 1.234,5678', 0);
   Visto que nesse exemplo o R$ não deveria existir então retornará zero.
   O sistema analisa a string da direita para a esquerda  a fim de
     determinar qual é o sinal de decimal, se ele encontrar primeiro a
     virgula, então a virgula será o decimal, se ele encontrar o ponto
     então o ponto será o decimal e o inverso será o separador de milhar
   by gladiston.santana[em]gmail.com
  */
  P_VALUE_AS_TEXT=trim(:P_VALUE_AS_TEXT);
  if (:P_VALUE_AS_TEXT is null) then
    P_VALUE_AS_TEXT=cast(P_VALUE_DEFAULT_IF_FAILS as varchar(30));
  P_VALUE_AS_TEXT=trim(:P_VALUE_AS_TEXT);
  if (:P_VALUE_AS_TEXT='') then
    P_VALUE_AS_TEXT=cast(P_VALUE_DEFAULT_IF_FAILS as varchar(30));
  if (position (',' in :P_VALUE_AS_TEXT)&gt;0) then
  begin
    P_VALUE_AS_TEXT=replace (:P_VALUE_AS_TEXT,  ',', '.');
  end

  P_VALUE_AS_TEXT=trim(:P_VALUE_AS_TEXT);
  return cast(P_VALUE_AS_TEXT AS numeric(18,4));
END

Modo de Usar

s='12.345,6789';
n=STR_TO_NUMERIC4(s, 0);
-- retorna 12345.6789
s='R$ 12.345,6789';
n=STR_TO_NUMERIC4(s, 0);
-- retorna zero porque R$ tornará o número inválido

Conclusão

O exemplo acima com pouca modificação você pode deixar o numero de casas decimais dinâmico. ou até mesmo permitir que ‘R$’ seja ignorado dentro de uma string, o ponto é, temos agora uma maneira simples de converter um texto para um numero.