Recebi um e-mail de um internauta que criou uma função em SQL que formata números. A função é bastante simples e pede apenas três parâmetros: valor, separador do milhar e separador decimal.

Segue abaixo o código:
CREATE FUNCTION FormataValor(@Valor DECIMAL(18,2),@SepMilhar CHAR(1),@SepDecimal CHAR(1))
RETURNS  VARCHAR(100) AS
BEGIN
DECLARE @inteiro VARCHAR(98),
@Texto VARCHAR(100),
@decimal VARCHAR(2),
@retorno VARCHAR(100)

SET @Texto = RTRIM(CAST(@Valor AS VARCHAR(50)))
SET @inteiro = CAST(CAST(@Valor AS INTEGER) AS VARCHAR(180))
SET @decimal = SUBSTRING(@Texto, LEN(@Texto)-1,2)
SET @retorno = ”

WHILE (LEN(@inteiro) > 3)
BEGIN
SET @retorno = @SepMilhar + SUBSTRING(@inteiro, LEN(@inteiro)-2, 3) + @retorno
SET @inteiro = SUBSTRING(@inteiro, 1, len(@inteiro)-3)
END

SET @retorno = @inteiro + @retorno
SET @retorno = @retorno + @SepDecimal + @decimal

RETURN @retorno
END

Crédito: Helder

Anúncios

Usando o CHARINDEX em SQL

dezembro 3, 2007

O CHARINDEX é uma função em SQL que retorna a primeira posição de uma sequencia de caracteres em uma determinada expressão. A primeira posição de contagem é 1.

Sintaxe:

CHARINDEX(expressao1, expressao2 [, posicao_inicial])

expressao1: É onde contém a sequencia de caracteres a ser encontrado.
expressao2: É onde contém a sequencia de caracteres que será feito a busca. Geralmente, é usado uma coluna de um campo.
posicao_inicial: É um parâmetro opcional, que deve-se colocar a posição inicial da busca, caso omitido, será iniciado do zero.

Segue abaixo a sintaxe:

SELECT

Nome,

CHARINDEX(‘papel’, Nome) AS Posicao

FROM

Produto

Na situação acima a busca será iniciada na primeira posição do campo ‘Nome’ da tabela ‘Produto’, porque o último parâmetro que indica a posição inicial de busca foi omitido.

O SQL possui uma função que faz a inverção dos caracteres passados como parâmetro. O parâmetro informado pode ser constante, variáveis, colunas retornadas de um campo e até mesmo um números, seu resultado sempre será VARCHAR.

Sintaxe:

REVERSE(expressão)

SELECT REVERSE(‘Alexandre’) AS ‘Nome’

O resultado da consulta acima será ‘erdnaxelA’, preservando os caracteres maiúsculos e/ou minúsculos.

A linguagem SQL possui uma função que exibe informações sobre todos os objetos existentes na base de dados.

Sintaxe:

OBJECTPROPERTY(id, propriedade)

Argumentos:

id -> É a identificação do objeto na base de dados, ou seja, uma identificação única e que pode ser encontrada na tabela sysobjects. Lembrando que este argumente DEVE ser do tipo inteiro.
propriedade -> É onde insere a expressão que se deseja obter sobre o objeto.

Segue abaixo todas as propriedades existentes no SQL Server:

Property name Object type Description and values returned
CnstIsClustKey Constraint A primary key with a clustered index. 1 = True0 = False
CnstIsColumn Constraint COLUMN constraint. 1 = True0 = False
CnstIsDeleteCascade Constraint A foreign key constraint with the ON DELETE CASCADE
option.
CnstIsDisabled Constraint Disabled constraint. 1 = True0 = False
CnstIsNonclustKey Constraint A primary key with a nonclustered index. 1 = True0 = False
CnstIsNotTrusted Constraint Constraint was enabled without checking existing rows, so constraint may not hold for all rows. 1 = True0 = False
CnstIsNotRepl Constraint The constraint is defined with the NOT FOR REPLICATION
keywords.
CnstIsUpdateCascade Constraint A foreign key constraint with the ON UPDATE CASCADE
option.
ExecIsAfterTrigger Trigger AFTER trigger.
ExecIsAnsiNullsOn Procedure, Trigger, View The setting of ANSI_NULLS at creation time. 1 = True0 = False
ExecIsDeleteTrigger Trigger DELETE trigger. 1 = True0 = False
ExecIsFirstDeleteTrigger Trigger The first trigger fired when a DELETE is executed against the
table.
ExecIsFirstInsertTrigger Trigger The first trigger fired when an INSERT is executed against the
table.
ExecIsFirstUpdateTrigger Trigger The first trigger fired when an UPDATE is executed against the
table.
ExecIsInsertTrigger Trigger INSERT trigger. 1 = True0 = False
ExecIsInsteadOfTrigger Trigger INSTEAD OF trigger.
ExecIsLastDeleteTrigger Trigger The last trigger fired when a DELETE is executed against the
table.
ExecIsLastInsertTrigger Trigger The last trigger fired when an INSERT is executed against the
table.
ExecIsLastUpdateTrigger Trigger The last trigger fired when an UPDATE is executed against the
table.
ExecIsQuotedIdentOn Procedure, Trigger, View The setting of QUOTED_IDENTIFIER at creation time. 1 = True0 = False
ExecIsStartup Procedure Startup procedure. 1 = True0 = False
ExecIsTriggerDisabled Trigger Disabled trigger. 1 = True0 = False
ExecIsUpdateTrigger Trigger UPDATE trigger. 1 = True0 = False
HasAfterTrigger Table, View Table or view has an AFTER trigger. 1 = True0 = False
HasInsertTrigger Table, View Table or view has an INSERT trigger. 1 = True0 = False
HasInsteadOfTrigger Table, View Table or view has an INSTEAD OF trigger. 1 = True0 = False
HasUpdateTrigger Table, View Table or view has an UPDATE trigger. 1 = True0 = False
IsAnsiNullsOn Function, Procedure, Table, Trigger, View Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = ON0 = OFF
IsCheckCnst Any CHECK constraint. 1 = True0 = False
IsConstraint Any Constraint. 1 = True0 = False
IsDefault Any Bound default. 1 = True0 = False
IsDefaultCnst Any DEFAULT constraint. 1 = True0 = False
IsDeterministic Function, View The determinism property of the function. Applies only to scalar- and table-valued functions. 1 = Deterministic0 = Not DeterministicNULL = Not a scalar- or table-valued function, or invalid object ID.
IsExecuted Any Specifies how this object can be executed (view, procedure, or trigger). 1 = True0 = False
IsExtendedProc Any Extended procedure. 1 = True0 = False
IsForeignKey Any FOREIGN KEY constraint. 1 = True0 = False
IsIndexed Table, View A table or view with an index.
IsIndexable Table, View A table or view on which an index may be created.
IsInlineFunction Function Inline function. 1 = Inline function0 = Not inline functionNULL = Not a function, or invalid object ID.
IsMSShipped Any An object created during installation of Microsoft® SQL Server™ 2000. 1 = True0 = False
IsPrimaryKey Any PRIMARY KEY constraint. 1 = True0 = False
IsProcedure Any Procedure. 1 = True0 = False
IsQuotedIdentOn Function, Procedure, Table, Trigger, View Specifies that the quoted identifier setting for the table is ON, meaning double quotation marks delimit identifiers in all expressions involved in the table definition. 1 = ON0 = OFF
IsReplProc Any Replication procedure. 1 = True0 = False
IsRule Any Bound rule. 1 = True0 = False
IsScalarFunction Function Scalar-valued function. 1 = Scalar-valued0 = Table-valuedNULL = Not a function, or invalid object ID.
IsSchemaBound Function, View A schema bound function or view created with SCHEMABINDING. 1 = Schema-bound0 = Not schema-boundNULL = Not a function or a view, or invalid object ID.
IsSystemTable Table System table. 1 = True0 = False
IsTable Table Table. 1 = True0 = False
IsTableFunction Function Table-valued function. 1 = Table-valued0 = Scalar-valuedNULL = Not a function, or invalid object ID.
IsTrigger Any Trigger. 1 = True0 = False
IsUniqueCnst Any UNIQUE constraint. 1 = True0 = False
IsUserTable Table User-defined table. 1 = True0 = False
IsView View View. 1 = True0 = False
OwnerId Any Owner of the object. Nonnull = The database user ID of the object owner.NULL = Invalid input.
TableDeleteTrigger Table Table has a DELETE trigger. >1 = ID of first trigger with given type.
TableDeleteTriggerCount Table The table has the specified number of DELETE triggers. >1 = ID of first trigger with given type.NULL = Invalid input.
TableFullTextBackground… Table The table has full-text background update index enabled. 1 = TRUE0 = FALSE
TableFulltextCatalogId Table The ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that
identifies the rows in a full-text indexed table.0 = Table is not full-text indexed.
TableFullTextChangeTr… Table The table has full-text change-tracking enabled. 1 = TRUE0 = FALSE
TableFulltextKeyColumn Table The ID of the column associated with the single-column unique index that is participating in the full-text index definition. 0 = Table is not full-text indexed.
TableFullTextPopulate… Table 0 = No population1 = Full population2 = Incremental population
TableHasActiveFulltext… Tables The table has an active full-text index. 1 = True0 = False
TableHasCheckCnst Table The table has a CHECK constraint. 1 = True0 = False
TableHasClustIndex Table The table has a clustered index. 1 = True0 = False
TableHasDefaultCnst Table The table has a DEFAULT constraint. 1 = True0 = False
TableHasDeleteTrigger Table The table has a DELETE trigger. 1 = True0 = False
TableHasForeignKey Table The table has a FOREIGN KEY constraint. 1 = True0 = False
TableHasForeignRef Table Table is referenced by a FOREIGN KEY constraint. 1 = True0 = False
TableHasIdentity Table The table has an identity column. 1 = True0 = False
TableHasIndex Table The table has an index of any type. 1 = True0 = False
TableHasInsertTrigger Table The object has an Insert trigger. 1 = True0 = FalseNULL = Invalid input.
TableHasNonclustIndex Table The table has a nonclustered index. 1 = True0 = False
TableHasPrimaryKey Table The table has a primary key. 1 = True0 = False
TableHasRowGuidCol Table The table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True0 = False
TableHasTextImage Table The table has a text column. 1 = True0 = False
TableHasTimestamp Table The table has a timestamp column. 1 = True0 = False
TableHasUniqueCnst Table The table has a UNIQUE constraint. 1 = True0 = False
TableHasUpdateTrigger Table The object has an Update trigger. 1 = True0 = False
TableInsertTrigger Table The table has an INSERT trigger. >1 = ID of first trigger with given type.
TableInsertTriggerCount Table The table has the specified number of INSERT triggers. >1 = ID of first trigger with given type.
TableIsFake Table The table is not real. It is materialized internally on demand by SQL Server. 1 = True0 = False
TableIsPinned Table The table is pinned to be held in the data cache. 1 = True0 = False
TableTextInRowLimit Table The maximum bytes allowed for text in row, or 0 if
text in row option is not set.
TableUpdateTrigger Table The table has an UPDATE trigger. >1 = ID of first trigger with given type.
TableUpdateTriggerCount Table The table has the specified number of UPDATE triggers. >1 = ID of first trigger with given type.

Nota: Tabela retirada do site MSDN

A propriedade IsIndexable pode consumir significantes recursos de processamento.
A propriedade TableHasActiveFulltextIndex retornará 1 (Verdadeiro) quando no mínimo uma coluna da tabela é adicionada com indíce.

Vamos aos exemplos:

A. Para descobrir se um determinado objeto (Veiculo) é uma VIEW, basta executar a seguinte instrução

IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISVIEW’) = 1
PRINT ‘Veiculo é uma VIEW’
ELSE
PRINT ‘Veiculo não é uma VIEW’

Observe que utilizei a função OBJECT_ID, esta função retorna a identificação do objeto passado como parâmetro, que deve ser o nome do objeto na base de dados. Foi utilizado esta função para não haver a necessidade de descobrir a identificação do objeto.

Caso a necessidade seja outra, como por exemplo, saber se o objeto é uma tabela, basta substituir a palavra-chave ISVIEW por ISTABLE. Como no exemplo abaixo:

IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISTABLE’) = 1
PRINT ‘Veiculo é uma TABLE’
ELSE
PRINT ‘Veiculo não é uma TABLE’

B. Saber se uma determinada tabela possui um campo do tipo TEXT, retornará 1 quando for verdadeiro e 0 quando for falso. Uma variável do tipo booleana.

SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableHasTextImage’)

C. Saber se uma determinada tabela possui uma chave-primária

SELECT OBJECTPROPERTY(OBJECT_ID(‘Fornecedor’), ‘TableHasPrimaryKey’)

D. Saber se uma determinada tabela possui uma TRIGGER SQL que remove (DELETE) registros

SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableDeleteTrigger’)

E. Descobrir qual o proprietário de um determinado objeto, Ex.: PROCEDURE, TRIGGER, TABLE, etc.

SELECT OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘OwnerId’)

Esta função retorna a identificação do proprietário do objeto na base de dados ou NULL caso passado o parâmetro incorretamente.

Finalizo este artigo por aqui, deixe seu comentário, críticas e/ou sugestões.

A função DATALENGTH tem o objetivo retornar o número de bytes usados para representar uma determinada expressão. Esta função é geralmente utilizada em variáveis do tipo VARCHAR, VARBINARY, TEXT, IMAGE, NVARCHAR e NTEXT que são tipos de dados que podem armazenar grandes quantidades de dados.

Muitos confundem a função DATALENGTH com a função LEN, que são totalmente diferentes, podendo retornar os mesmos valores. A função LEN tem como intuito retornar a quantidade de caracteres existentes em uma determinada expressão.

SELECT

LEN(CPF) AS CPF

FROM

Cliente

A instrução acima mostrará a quantidade de caracteres existentes no campo ‘CPF’ da tabela ‘Cliente’.

SELECT

DATALENGTH(CPF) AS CPF

FROM

Cliente

Já esta outra consulta informa o número de bytes que o campo de um determinado registro está ocupando na base de dados. Quando o campo é NULL, ou seja, nulo em português, significa que o campo não tem valor nenhum e o retorno da função DATALENGTH é nulo enquanto o retorno da função LEN é 0, indicando que não possue nenhum caracter na expressão.

A função ISNUMERIC do SQL informa se o campo passado por parâmetro é do tipo número, retornando uma variável booleana (verdadeiro ou falso). A sintaxe é simples e não possue nenhum segredo.

SELECT

ISNUMERIC(RG) AS [Número?]

FROM

Cliente

Esta função retorna uma variável booleana, porém em binário, indicando 0 quando for falso (não é número) e 1 quando for verdadeiro (número). É uma função muito útil que elimina a possibilidade de criar uma função no retorno do SQL.

Formatando datas em SQL

outubro 19, 2007

Geralmente é preciso formatar a data que é retornada em uma instrução SQL, de uma maneira que poderia gerar uma lógica um pouco complicada e complexa. Com este intuito, a linguagem SQL possui uma função (e muitas outras) de formatação de datas.

Uma consulta que achei muito útil é a de poder capturar o dia do ano. Veja a instrução a seguir:

SELECT

DATEPART(dy, GETDATE()) AS ‘Dia do Ano’

Existe outro tipo de formatação que retorna o quadrimestre da data que for passada como parâmetro, observe:

SELECT

DATEPART(qq, ‘2007-10-19’) AS ‘Quadrimestre do Ano’

Passando esta data como parâmetro irá mostrar 4.

SELECT

DATEPART(dw, GETDATE()) AS ‘Dia da Seman’

O exemplo acima irá retornar o dia da semana correspondente de uma determinada data.

Possuem outros tipos de formatação e que podem ser úteis algum dia.