Pular para o conteúdo principal

Treinamento para consultas com SQL Server - Parte 5 - Funções

A especificação da linguagem SQL ANSI prevê algumas funções incorporadas a esta. O SQL Server possui além das que são padrão suas próprias implementações além de permitir que o usuário crie suas próprias.

Além de consultar a documentação do produto outra forma de se conhecer quais funções estão disponíveis é através da ferramenta SSMS na janela Pesquisador de Objetos, dentro do banco de dados selecionado e procurar o item Programação > Funções > Funções do sistema. Estes locais mostram a lista das funções suportadas pela versão do SQL Server instalada conforme está exibido na figura a seguir.

Neste tópico serão abordadas as principais destas e que são as mais usadas no trabalho diário.

Funções de agregação

Permitem fazer operações de soma, contagem, média, etc. sobre colunas.

Caso dentro da consulta existam colunas que não estejam cobertas por uma função deste tipo, as mesmas devem vir enumeradas e precedidas pela cláusula GROUP BY.

  1. SUM(). Retorna a soma de uma expressão que pode ser uma coluna, várias, uma operação matemática contendo ou não uma coluna.
  2. COUNT(). Conta o número de linhas dentro de uma determinada tabela que satisfaçam uma condição. Colunas com valor NULL não são contadas.
  3. MAX(). Retorna o valor máximo (número, data, texto) para uma expressão.
  4. MIN(). Idem retornando o valor mínimo.
  5. AVG(). Retorna a média aritmética. Valores nulos são ignorados.

Funções de data e hora

Estas funções são usadas para fazerem conversões e operações com datas.

  1. CURRENT_TIMESTAMP. Mostra a data e hora (incluindo segundos e milissegundos) correspondentes ao servidor onde o SQL Server está sendo executado.
  2. DATEADD(). Faz operações de adição de data. Devem ser informados:
    1. Tipo de intervalo a ser calculado que pode ser: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
    2. Valor a ser acrescentado. Podem ser usados números negativos
    3. Data de referência para executar a operação
    4. Exemplo: select dateadd(YEAR, -2, GETDATE())
  3. DATEDIFF(). Calcula a diferença entre duas datas usando o intervalo passado como parâmetro. Esta função recebe os seguintes argumentos:
    1. Tipo de intervalo a ser calculado que pode ser: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
    2. Primeira data de referência
    3. Segunda data
    4. Exemplo: select DATEDIFF(YEAR, '2011-07-01 00:00:00', getdate())
  4. DAY(). Extrai o dia do mês de uma data informada.
  5. MONTH(). Extrai o número do mês de uma data.
  6. YEAR(). Extrai o ano de uma data.
  7. GETDATE().Mostra a data e hora (incluindo segundos e milissegundos) correspondentes ao servidor onde o SQL Server está sendo executado. Semelhante a CURRENT_TIMESTAMP.
  8. WEEKDAY(). Mostra o dia da semana para uma data.
  9. DATEPART(). Extrai uma parte específica de uma data sendo as mais comuns:
    1. day
    2. quarter
    3. month
    4. dayofyear
    5. week
    6. weekday
    7. hour
    8. minut
    9. second
    10. Exemplo: select datepart(day, '2015-06-15')

    Funções matemáticas

    1. ABS(). Retorna o valor absoluto de um número, ou seja, ignora o sinal. Exemplo: SELECT ABS(-1000).
    2. SIGN(). Informa -1 para números negativos e 1 para positivos. Exemplo: SELECT SIGN(-1000).
    3. SQRT(). Retorna a raiz quadrada de um número. Exemplo: SELECT SQRT(144).
    4. SQUARE(). Retorna um número correspondente ao quadrado do número passado. Exemplo: SELECT SQUARE(3).
    5. POWER(). Eleva um número à potência selecionada. Exemplo: SELECT POWER(3, 3).

    Funções de metadados

    Retornam informações sobre a estrutura dos bancos. São mais comuns em funções, triggers e rotinas de administração do banco. Algumas, como a do exemplo abaixo, podem ter alguma utilidade ao se escrever rotinas de automação, log de updates, etc.

    COL_LENGTH()

    Mostra o tamanho da coluna em bytes recebendo o nome da tabela e da coluna.

    SELECT col_length('Sales.SalesOrderHeader', 'subtotal')
    
    -- Tipo da coluna: Money
    -- Resultado: 8
    		

    Outras funções

    Existem outras bastante específicas que permitem verificar os dados e realizar conversões. Entre estas, as que são mais usadas:

    1. CAST(). Converte entre tipos de dados.
    2. COALESCE(). Substitui valores nulos por um valor definido.
    3. ISNULL(). Substitui valores nulos por um valor definido. Semelhante a COALESCE().
    4. CONVERT(). Converte entre tipos de dados permitindo definir formatos para alguns tipos como data.
    5. ISNUMERIC(). Verifica se uma expressão contém um valor numérico.

    Funções de string

    São aplicadas sobre textos para realizar operações diversas. Boa parte destas funções são exclusivas do SQL Server. Se estiver trabalhando com outro banco consulte a documentação para verificara quais as equivalências.

    1. ASCII(). Retorna o código ASCII de um caractere.
    2. CHAR(). Converte um valor numérico para o seu caractere correspondente na tabela ASCII.
    3. LEFT(). Retorna os ‘n’ caracteres à esquerda de um texto a partir do início. Exemplo:SELECT LEFT('VLADIMIR RECH', 8)
    4. LEN(). Informa o número de caracteres. Exemplo: SELECT LEN('VLADIMIR RECH').
    5. LOWER(). Converte os caracteres de um texto para minúsculas.
    6. LTRIM(). Remove espaços em branco à esquerda de um texto.
    7. PATINDEX(). Retorna a posição da primeira ocorrência de um caractere em um texto. Exemplo: SELECT PATINDEX('%L%', 'FORLOGIC')
    8. QUOTENAME(). Insere os delimitadores padrão do SQL Server para nomes de colunas “[]”.
    9. REPLACE(). Substitui um conjunto de caracteres por outro. Exemplo: SELECT REPLACE('FORLOGIC', 'L', '4').
    10. REPLICATE(). Repete um caractere um número de vezes específico. Exemplo: SELECT REPLICATE('0', 10).
    11. REVERSE(). Inverte um texto.
    12. RIGHT(). Retorna os ‘n’ caracteres à direita de um texto. Exemplo: SELECT RIGHT('FORLOGIC', 5).
    13. RTRIM(). Remove caracteres em branco à direita de um texto.
    14. SOUNDEX(). Realiza busca pela semelhança sonora.
    15. STUFF(). Substitui um caractere por outro em uma posição e número de vezes específico.
    16. SUBSTRING(). Retorna um subconjunto de caracteres de um texto, iniciando em uma posição específica e retornando o número de caracteres passado como argumento.
    17. UPPER(). Converte os caracteres de um texto para letras maiúsculas.

    Se quiser se aprofundar nas funções nativas do SQL Server recomendo consultar a documentação oficial da Microsoft. Até a próxima e boa pesquisa.


Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…