quinta-feira, 28 de janeiro de 2010

Função no SQL Server para escrever um valor por extenso

Essa função já deu o que falar. Na primeira publicação em agosto de 2003 (http://www.linhadecodigo.com/Dica.aspx?id=1091), havia um pequeno problema com alguns valores. Mas como eu não conseguia corrigir a dica no site da linha de código, até hoje eu recebo e-mails do pessoal dizendo que está com problema e procurando ajuda.

Eis a versão corrigida.

Abaixo está uma função, que julgo ser de muita utilidade, para os desenvolvedores que estão utilizando o MSSQLServer. Este tipo de função, tem uma perfomance muito melhor quando está no Banco de Dados do que no código de um programa em Delphi, VB, C# e etc. Ela, recebe como parametro um Número, e retorna o valor por extenso do mesmo.
E além do mais, o desenvolvedor pode parametrizar a moeda ou a unidade de medida, e ainda, exibir ou não os centavos.

As seguir estão os códigos, e mais abaixo um exemplo:

-- Aqui comeca o Script
ALTER FUNCTION dbo.Extenso(@VALOR DECIMAL(18, 5)) RETURNS VARCHAR(255)
AS
BEGIN
DECLARE
@STR_EXT VARCHAR(255),
@FLAG_E INT,
@GRUPO DECIMAL(10, 2),
@MOEDA VARCHAR(10),
@MOEDA_PLURAL VARCHAR(10),
@FLAG_CENTAVOS DECIMAL(18, 5)
-- Aqui vc podera configurar a descricao da Moeda
SET @MOEDA = 'Real'
SET @MOEDA_PLURAL = 'Reais'
SET @FLAG_CENTAVOS = 1 -- Exibir os centavos [ 0) Nao 1) Sim ]

SET @STR_EXT = ''
SET @FLAG_E = 0
SET @GRUPO = 0

IF ((CONVERT(INT, @VALOR) - (CONVERT(INT, @VALOR) % 1)) = 0)
BEGIN
SET @STR_EXT = ' Zero'
END
ELSE
BEGIN
DECLARE @TEMPINT BIGINT
SET @TEMPINT = .000001*((CONVERT(INT, @VALOR) % 1000000000)
- (CONVERT(INT, @VALOR) % 1000000))
SELECT @FLAG_E = FLAG_E, @STR_EXT = STR_EXT
FROM dbo.TrataGrupoExtenso(
@TEMPINT, ' Milhão' , ' Milhões', @FLAG_E, @STR_EXT)
SET @TEMPINT = .001*((CONVERT(INT, @VALOR) % 1000000) -
(CONVERT(INT, @VALOR) % 1000))
SELECT @FLAG_E = FLAG_E, @STR_EXT = STR_EXT
FROM dbo.TrataGrupoExtenso(
@TEMPINT, ' Mil' , ' Mil', @FLAG_E, @STR_EXT)
SET @TEMPINT = (CONVERT(INT, @VALOR) % 1000)
SELECT @FLAG_E = FLAG_E, @STR_EXT = STR_EXT
FROM dbo.TrataGrupoExtenso(
@TEMPINT, '' , '', @FLAG_E, @STR_EXT)
END
IF (ROUND(@VALOR, 0) = 1)
BEGIN
SET @STR_EXT = @STR_EXT + ' '+RTRIM(@MOEDA)
END
ELSE
BEGIN
IF (ROUND(@VALOR, -6) <> 0) and (ROUND(@VALOR, 0) - ROUND(@VALOR, -6) = 0)
SET @STR_EXT = @STR_EXT + ' de ' + RTRIM(@moeda_plural)
ELSE
SET @STR_EXT = @STR_EXT + ' ' + RTRIM(@moeda_plural)
END

IF (@FLAG_CENTAVOS = 1)
BEGIN
SET @FLAG_E = 1;
DECLARE @TEMPINT2 BIGINT
SET @TEMPINT2 = 100*(@VALOR - FLOOR(@VALOR))

-- Aqui vc podera mudar a descricao dos centavos
SELECT @FLAG_E = FLAG_E, @STR_EXT = STR_EXT
FROM dbo.TrataGrupoExtenso(
@TEMPINT2, ' centavo' , ' centavos', @FLAG_E, @STR_EXT)
END
RETURN(@STR_EXT)
END
GO

ALTER FUNCTION dbo.TrataGrupoExtenso(
@GRUPO DECIMAL(18, 5), @SINGULAR VARCHAR(50),
@PLURAL VARCHAR(50), @FLAG_E INT, @STR_EXT VARCHAR(255))
RETURNS @RESULTADO TABLE (FLAG_E INT, STR_EXT varchar(250))
AS
BEGIN
DECLARE
@RETORNO VARCHAR(255),
@FLAG INT

SET @RETORNO = @STR_EXT
SET @FLAG = @FLAG_E

IF (@GRUPO <> 0)
BEGIN
IF (@FLAG_E = 1)
SET @RETORNO = @RETORNO + ' e'
SET @FLAG_E = 1
SET @RETORNO = @RETORNO + (SELECT dbo.GrupoExtenso(@grupo))
IF (@GRUPO = 1) SET @RETORNO = @RETORNO + @SINGULAR
ELSE SET @RETORNO = @RETORNO + @PLURAL
END
INSERT @RESULTADO
(FLAG_E, STR_EXT) VALUES (@FLAG, @RETORNO)
RETURN
END
GO

ALTER FUNCTION dbo.GrupoExtenso(@VALOR DECIMAL(18, 2))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE
@STR_EXT VARCHAR(255),
@AUX INT,
@VALOR_T INT

SET @STR_EXT = ''
SET @AUX = 0

SET @VALOR_T = ROUND(@VALOR, 0)
SET @AUX = @VALOR_T - (@VALOR_T % 100)


IF (@VALOR_T = 100) SET @STR_EXT = ' Cem'
ELSE IF (@AUX = 100) SET @STR_EXT = ' Cento'
ELSE IF (@AUX = 200) SET @STR_EXT = ' Duzentos'
ELSE IF (@AUX = 300) SET @STR_EXT = ' Trezentos'
ELSE IF (@AUX = 400) SET @STR_EXT = ' Quatrocentos'
ELSE IF (@AUX = 500) SET @STR_EXT = ' Quinhentos'
ELSE IF (@AUX = 600) SET @STR_EXT = ' Seiscentos'
ELSE IF (@AUX = 700) SET @STR_EXT = ' Setecentos'
ELSE IF (@AUX = 800) SET @STR_EXT = ' Oitocentos'
ELSE IF (@AUX = 900) SET @STR_EXT = ' Novecentos'

IF (((@VALOR_T - @AUX) <> 0) AND (@AUX <> 0) and (UPPER(SUBSTRING(RTRIM(@STR_EXT), LEN(RTRIM(@STR_EXT))-1, 2)) <> ' E'))
SET @STR_EXT = @STR_EXT+' e'

SET @AUX = (@VALOR_T % 100) - (@VALOR_T % 10)

IF (@AUX = 10)
BEGIN
SET @AUX = (@VALOR_T % 10)
IF (@AUX = 0) SET @STR_EXT = @STR_EXT+' Dez'
ELSE IF (@AUX = 1) SET @STR_EXT = @STR_EXT+' Onze'
ELSE IF (@AUX = 2) SET @STR_EXT = @STR_EXT+' Doze'
ELSE IF (@AUX = 3) SET @STR_EXT = @STR_EXT+' Treze'
ELSE IF (@AUX = 4) SET @STR_EXT = @STR_EXT+' Quatorze'
ELSE IF (@AUX = 5) SET @STR_EXT = @STR_EXT+' Quinze'
ELSE IF (@AUX = 6) SET @STR_EXT = @STR_EXT+' Dezesseis'
ELSE IF (@AUX = 7) SET @STR_EXT = @STR_EXT+' Dezessete'
ELSE IF (@AUX = 8) SET @STR_EXT = @STR_EXT+' Dezoito'
ELSE IF (@AUX = 9) SET @STR_EXT = @STR_EXT+' Dezenove'
END
ELSE
BEGIN
IF (@AUX = 20) SET @STR_EXT = @STR_EXT+' Vinte'
ELSE IF (@AUX = 30) SET @STR_EXT = @STR_EXT+' Trinta'
ELSE IF (@AUX = 40) SET @STR_EXT = @STR_EXT+' Quarenta'
ELSE IF (@AUX = 50) SET @STR_EXT = @STR_EXT+' Cinquenta'
ELSE IF (@AUX = 60) SET @STR_EXT = @STR_EXT+' Sessenta'
ELSE IF (@AUX = 70) SET @STR_EXT = @STR_EXT+' Setenta'
ELSE IF (@AUX = 80) SET @STR_EXT = @STR_EXT+' Oitenta'
ELSE IF (@AUX = 90) SET @STR_EXT = @STR_EXT+' Noventa'

IF (((@VALOR_T % 10) <> 0))
BEGIN
IF ((@STR_EXT <> '') and (UPPER(SUBSTRING(RTRIM(@STR_EXT), LEN(RTRIM(@STR_EXT))-1, 2)) <> ' E'))
SET @STR_EXT = @STR_EXT+' e'
SET @AUX = (@VALOR_T % 10)
IF (@AUX = 1) SET @STR_EXT = @STR_EXT+' Um'
ELSE IF (@AUX = 2) SET @STR_EXT = @STR_EXT+' Dois'
ELSE IF (@AUX = 3) SET @STR_EXT = @STR_EXT+' Tres'
ELSE IF (@AUX = 4) SET @STR_EXT = @STR_EXT+' Quatro'
ELSE IF (@AUX = 5) SET @STR_EXT = @STR_EXT+' Cinco'
ELSE IF (@AUX = 6) SET @STR_EXT = @STR_EXT+' Seis'
ELSE IF (@AUX = 7) SET @STR_EXT = @STR_EXT+' Sete'
ELSE IF (@AUX = 8) SET @STR_EXT = @STR_EXT+' Oito'
ELSE IF (@AUX = 9) SET @STR_EXT = @STR_EXT+' Nove'
END
END
RETURN(@STR_EXT);
END
GO

-- Aqui termina o Script

----------------------------------------------------

-- Aqui comeca o teste
SELECT dbo.Extenso(1820)

SELECT dbo.Extenso(1820.23)
-- Aqui termina o teste

Função MODULO 11 para o SQL Server

Conforme eu havia publicado em Junho de 2004 (http://www.linhadecodigo.com/Dica.aspx?id=1158)

Módulo 11 para cálculo do dígito verificador de Agência, Código de Cedente, Nosso Numero e etc.
Eu passei um bom tempo procurando na web um script da função Modulo 11, para o MSSQLServer, e não encontrei. Então resolvi desenvolver uma e compartilhar com vocês.

Caso ocorra algum problema, por favor entre em contato.

Abraço,

Elton Bicalho

Em seguida está o código do script.

CREATE FUNCTION Modulo11(@VALOR varchar(60))
RETURNS CHAR(1)
AS
BEGIN
DECLARE
@SOMA INT,
@CONTADOR INT,
@PESO INT,
@DIGITO INT,
@RETORNO CHAR(1),
@BASE INT,
@RESTO BIT

SET @SOMA = 0
SET @PESO = 2
SET @BASE = 9
SET @RESTO = 0
SET @CONTADOR = Len(@VALOR)

LOOP:
BEGIN
SET @SOMA = @SOMA + (Convert(int, SubString(@VALOR, @CONTADOR, 1)) *
@PESO)
IF (@PESO < @BASE)
SET @PESO = @PESO + 1
ELSE
SET @PESO = 2
SET @CONTADOR = @CONTADOR-1
END
IF @CONTADOR >= 1 GOTO LOOP

IF (@RESTO = 1)
BEGIN
SET @RETORNO = (@SOMA % 11)
END
ELSE
BEGIN
SET @DIGITO = 11 - (@SOMA % 11)
IF (@DIGITO > 9) SET @DIGITO = 0
SET @RETORNO = @DIGITO
END
RETURN @RETORNO
END
GO

/* Exemplo:
Calculando o Digito verificador do Banco do Brasil
*/
SELECT dbo.Modulo11('98710223423')

Função Modulo 10, para SQL Server, muito usada para calculo de digitos verificadores SQL Server

Conforme eu havia publicado em Junho de 2004 (http://www.linhadecodigo.com/Dica.aspx?id=1157)

Função Modulo 10, para SQL Server, muito usada para calculo de digitos verificadores SQL Server, muito usado em boletos bancários e validação de cartões de crédito e etc.

CREATE FUNCTION Modulo10(@numero varchar(15))
RETURNS CHAR(1)
AS
BEGIN

declare
@max smallint,
@contador smallint,
@peso varchar(24),
@resto smallint,
@valor smallint,
@soma int,
@retorno int
select @peso = '121212121212121212121212'
select @max = datalength(@numero)
select @peso = right(@peso, @max)
set @contador = @max+1
set @soma = 0
loop:
set @contador = @contador-1
set @valor = isnull((ascii(substring(@peso, @contador, 1))-48) *
(ascii(substring(@numero, @contador, 1))-48), 0)

set @soma = isnull((select (case when (@valor<10)>
@valor when (@valor>9) then @valor-10 end)), 0)+@soma

set @soma = isnull((select (case when (@valor<10)>
null when (@valor>9) then 1 end)), 0)+@soma
if (@contador >1) goto loop

select @resto= sum(@soma)%10

select @retorno = case @resto when 0 then 0 else 10-@resto end

return(Convert(char(1), @retorno))

END
GO

-- Agora o teste:
SELECT dbo.Modulo10('3438')

Transforma a primeira letra em maiúscula e o restante em minúsculas no SQL Server

Conforme eu havia publicado Junho de 2004 (http://www.linhadecodigo.com.br/Dica.aspx?id=1164)

Esta função transforma a primeira letra de uma palavra em maiúscula e o restante em minúscula.
Para quem conhece o Oracle, existe uma função nativa que faz este trabalho.

No SQL não existe.

Foi pensando em suprir esta deficiencia que implementei esta função.

Abaixo o script seguido de um exemplo:

CREATE FUNCTION InitCap(@Texto VARCHAR(255))
RETURNS Varchar(255)
AS BEGIN
DECLARE @TextoTemp VARCHAR(255)
DECLARE @i INT
SET @TextoTemp = LOWER(@Texto)
SET @TextoTemp = UPPER(LEFT(@TextoTemp,1)) +
SUBSTRING(@TextoTemp,2,LEN(@TextoTemp))

WHILE CHARINDEX(' ',@TextoTemp,1) > 0
BEGIN
SET @i = CHARINDEX(' ',@TextoTemp,1)
SET @TextoTemp = LEFT(@TextoTemp,@i-1) + '~*' +
UPPER(SUBSTRING(@TextoTemp,@i + 1,1)) +
SUBSTRING(@TextoTemp,@i+2,LEN(@TextoTemp))
END

SET @TextoTemp = REPLACE(@TextoTemp,'~*',' ')
IF (PATINDEX('% da %', @TextoTemp) > 0)
BEGIN
SET @TextoTemp = STUFF(@TextoTemp, PATINDEX('% da %', @TextoTemp), 4, ' da ')
END
IF (PATINDEX('% das %', @TextoTemp) > 0)
BEGIN
SET @TextoTemp = STUFF(@TextoTemp, PATINDEX('% das %', @TextoTemp), 5, ' das ')
END
IF (PATINDEX('% de %', @TextoTemp) > 0)
BEGIN
SET @TextoTemp = STUFF(@TextoTemp, PATINDEX('% de %', @TextoTemp), 4, ' de ')
END
IF (PATINDEX('% do %', @TextoTemp) > 0)
BEGIN
SET @TextoTemp = STUFF(@TextoTemp, PATINDEX('% do %', @TextoTemp), 4, ' do ')
END
IF (PATINDEX('% dos %', @TextoTemp) > 0)
BEGIN
SET @TextoTemp = STUFF(@TextoTemp, PATINDEX('% dos %', @TextoTemp), 5, ' dos ')
END
SET @Texto = @TextoTemp
RETURN @Texto
END
GO

SELECT dbo.InitCap('FULANO BELTRANO DA SILVA')
SELECT dbo.InitCap('fulano beltrano da silva')