Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Valor em moeda BR

By wamj,

/*-------------------------------------------------------------------------------------------------------------------------------

Essa função retorna os valores formatados no padrão da moeda brasileira R$.Adaptção do script para formatar valores monetários (US$) disponível em:http://www.sqlservercentral.com/scripts/Miscellaneous/31840/

Criado por: Wilson Junior - 26/05/2011wilsonamjunior@gmail.com

*/-------------------------------------------------------------------------------------------------------------------------------

CREATE FUNCTION FN_currencyBR (@monetary_value decimal(20,2) ) returns varchar(20)
as
begin
    declare @return_value varchar(20)
    declare @is_negative bit
    select @is_negative = case when @monetary_value<0 then 1 else 0 end

    if @is_negative = 1
        set @monetary_value = -1*@monetary_value

    set @return_value = convert(varchar, isnull(@monetary_value, 0))
    
    
    
    declare @before varchar(20), @after varchar(20)

    if charindex ('.', @return_value )>0
    begin
        set @after= substring(@return_value,  charindex ('.', @return_value ), len(@return_value))
        set @before= substring(@return_value,1,  charindex ('.', @return_value )-1)    
    end
    else
    begin
        set @before = @return_value
        set @after=''
    end
    -- after every third character:
    declare @i int
    if len(@before)>3
    begin
        set @i = 3
        while @i>1 and @i < len(@before)
        begin
            set @before = substring(@before,1,len(@before)-@i) + '.' + right(@before,@i)
            set @i = @i + 4
        end
    end
    set @return_value = @before + replace(@after,'.',',')    


    if @is_negative = 1
        set @return_value = '-' + @return_value

    return @return_value
end

Total article views: 112 | Views in the last 30 days: 1
 
Related Articles
FORUM

Getting the data before begining of a Transaction

Getting the data before begining of a Transaction

FORUM

Primary Key creation - before and after bulk insert

Primary Key creation - before and after bulk insert

FORUM

Index creation - Before OR AFTER Data?

Create index before data population or after?

SCRIPT

Extarcting string after and before a Character/Pattern

Finding characters/string before and after some character or pattern

FORUM

Index creation after of before populate?

is better to create the index after of before?

Tags
br    
brasileira    
brasileiro    
currency    
moeda    
money    
 
Contribute