14/01/13 Funções para conversão de tipos

1/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

SQL em Oracle > DML > Funções > Funções de linha > Funções para conversão de tipos

Funções para conversão de tipos

A linguagem SQL fornece várias funções para controlar a conversão de tipos de dados. Estas funções convertem um valor de um tipo

para outro tipo.

TO_DATE(string)

TO_CHAR(data)EXTRACT(YEAR|MONTH|WEEK|DAY|HOUR|MINUTE|TIMEZONE FROM DATE|TIMESTAMP)TO_CHAR(número)

TO_NUMBER(string)MASCARA_DATAO ORACLE e o ano 2000MASCARA_NUMERONVL( )

GREATEST( )DECODE( )CASE

TO_DATE(string, máscara de data)

A função TO_DATE() converte do formato cadeia de caracteres para data, seguindo a máscara escolhida pelo utilizador. Os valores

possíveis para a mascara estão descritos nesta página.

alter session set nls_territory='PORTUGAL';alter session set nls_language='PORTUGUESE';alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss')+1from dual;

alter session set succeeded.

alter session set succeeded.

alter session set succeeded.

TO_DATE('2006-07-1712:15:45','YYYY-MM-DDHH24:MI:SS')+1 ------------------------- 2006-07-18 12:15:45

1 rows selected

A função TO_DATE() converte de cadeia de caracteres em data. À data resultante adicionamos um dia;A data obtida pelo comando anterior é apresentada usando o formato definido pelo comando ALTER SESSION SETNLS_DATE_FORMAT;

Topo

TO_CHAR(data, máscara_data, [nls_date_format] )

A função TO_CHAR(data) converte do formato data para cadeia de caracteres, seguindo a máscara escolhida pelo utilizador. Os

valores possíveis para a mascara estão descritos nesta página.

14/01/13 Funções para conversão de tipos

2/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

alter session set nls_territory='PORTUGAL';alter session set nls_language='PORTUGUESE';alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select ename, hiredate, to_char(hiredate,'day, dd "de" Month "de" YYYY')from emp;

alter session set succeeded.

alter session set succeeded.

alter session set succeeded.

ENAME HIREDATE TO_CHAR(HIREDATE,'DAY,DD"DE"MONTH"DE"YYYY') ---------- ------------------------- ------------------------------------------- SMITH 1980-12-17 00:00:00 quarta-feira , 17 de Dezembro de 1980 ALLEN 1981-02-20 00:00:00 sexta-feira , 20 de Fevereiro de 1981 WARD 1981-02-22 00:00:00 domingo , 22 de Fevereiro de 1981 JONES 1981-04-02 00:00:00 quinta-feira , 02 de Abril de 1981 MARTIN 1981-09-28 00:00:00 segunda-feira, 28 de Setembro de 1981 BLAKE 1981-05-01 00:00:00 sexta-feira , 01 de Maio de 1981 CLARK 1981-06-09 00:00:00 terça-feira , 09 de Junho de 1981 SCOTT 1982-12-09 00:00:00 quinta-feira , 09 de Dezembro de 1982 KING 1981-11-17 00:00:00 terça-feira , 17 de Novembro de 1981 TURNER 1981-09-08 00:00:00 terça-feira , 08 de Setembro de 1981 ADAMS 1983-01-12 00:00:00 quarta-feira , 12 de Janeiro de 1983 JAMES 1981-12-03 00:00:00 quinta-feira , 03 de Dezembro de 1981 FORD 1981-12-03 00:00:00 quinta-feira , 03 de Dezembro de 1981 MILLER 1982-01-23 00:00:00 sábado , 23 de Janeiro de 1982

14 rows selected

Colocar a máscara em maiúsculas ou minúsculas influencia o resultado:

alter session set nls_territory='PORTUGAL';alter session set nls_language='PORTUGUESE';alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select ename, hiredate, to_char(hiredate,'DAY, DD "de" MONTH "de" YYYY')from emp;

alter session set succeeded.

alter session set succeeded.

alter session set succeeded.

ENAME HIREDATE TO_CHAR(HIREDATE,'DAY,DD"DE"MONTH"DE"YYYY') ---------- ------------------------- ------------------------------------------- SMITH 1980-12-17 00:00:00 QUARTA-FEIRA , 17 de DEZEMBRO de 1980 ALLEN 1981-02-20 00:00:00 SEXTA-FEIRA , 20 de FEVEREIRO de 1981 WARD 1981-02-22 00:00:00 DOMINGO , 22 de FEVEREIRO de 1981 JONES 1981-04-02 00:00:00 QUINTA-FEIRA , 02 de ABRIL de 1981 MARTIN 1981-09-28 00:00:00 SEGUNDA-FEIRA, 28 de SETEMBRO de 1981 BLAKE 1981-05-01 00:00:00 SEXTA-FEIRA , 01 de MAIO de 1981 CLARK 1981-06-09 00:00:00 TERÇA-FEIRA , 09 de JUNHO de 1981 SCOTT 1982-12-09 00:00:00 QUINTA-FEIRA , 09 de DEZEMBRO de 1982 KING 1981-11-17 00:00:00 TERÇA-FEIRA , 17 de NOVEMBRO de 1981 TURNER 1981-09-08 00:00:00 TERÇA-FEIRA , 08 de SETEMBRO de 1981 ADAMS 1983-01-12 00:00:00 QUARTA-FEIRA , 12 de JANEIRO de 1983 JAMES 1981-12-03 00:00:00 QUINTA-FEIRA , 03 de DEZEMBRO de 1981 FORD 1981-12-03 00:00:00 QUINTA-FEIRA , 03 de DEZEMBRO de 1981 MILLER 1982-01-23 00:00:00 SÁBADO , 23 de JANEIRO de 1982

14 rows selected

14/01/13 Funções para conversão de tipos

3/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

alter session set nls_territory='PORTUGAL';alter session set nls_language='PORTUGUESE';alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'hh:mi:ss') "Hora Minutos Segundos", to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'scc' ) "Século", to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'syear' ) "Ano por extenso", to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'q' ) "Trimestre", to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'HH24' ) "Hora em formato 24", to_char(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'), 'HH' ) "Hora formato 12"from dual;

alter session set succeeded.

alter session set succeeded.

alter session set succeeded.

Hora Minutos Segundos Século Ano por extenso Trimestre Hora em formato 24 Hora formato 12 --------------------- ------ ----------------- --------- ------------------ --------------- 12:15:45 21 two thousand six 3 12 12

1 rows selected

É possível alterar a lingua com que são escritos os meses e dias da semana usando o parâmetro NLS_DATE_FORMAT dentro da

própria função:

select to_char(sysdate,'Mon dd hh24:mi:ss','nls_date_language=american') from dual;

Dec 19 16:30:07

Topo

EXTRACT(YEAR|MONTH|WEEK|DAY|HOUR|MINUTE|TIMEZONE FROM DATE|TIMESTAMP)

A função EXTRACT surgiu na versão 9i e permite extrair as componentes de uma data. A sua sintaxe genérica é:

EXTRACT ({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }| { TIMEZONE_HOUR | TIMEZONE_MINUTE }| { TIMEZONE_REGION | TIMEZONE_ABBR }FROM { date_value | interval_value } )

Tem as seguintes limitações:

De um tipo DATE apenas podemos extrair o ano, o mês e o dia;Só podemos extrair TIMEZONE_HOUR e TIMEZONEMINUTE de um tipo TIMESTAMP que possua TIMEZONE;

Seguem alguns exemplos de utilização:

SELECT EXTRACT(YEAR FROM DATE '2012-05-01') FROM dual;SELECT EXTRACT(MONTH FROM DATE '2012-05-01') FROM dual;SELECT EXTRACT(DAY FROM DATE '2012-05-01') FROM dual;

Select extract(year from sysdate) from dual;Select extract(month from sysdate) from dual;Select extract(day from sysdate) from dual;

14/01/13 Funções para conversão de tipos

4/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

Select extract(year from systimestamp) from dual;Select extract(month from systimestamp) from dual;Select extract(day from systimestamp) from dual;Select extract(hour from systimestamp) from dual;Select extract(minute from systimestamp) from dual;Select extract(second from systimestamp) from dual;Select extract(timezone_hour from systimestamp) from dual;Select extract(timezone_minute from systimestamp) from dual;Select extract(timezone_region from systimestamp) from dual;Select extract(timezone_abbr from systimestamp) from dual;

Topo

TO_CHAR(número, máscara de número)

A função TO_CHAR(número) converte do formato numérico para cadeia de caracteres, seguindo a máscara escolhida pelo utilizador.

Os valores possíveis para a mascara estão descritos nesta página.

A mascara '$999,999.99' representa uma quantia monetária que possui parte décimal e separador de milhares. O resultado aparece

sempre em formato americano, independentemente das variáveis NLS:

select to_char(sal,'$999,999.99') from emp;

TO_CHAR(SAL,'$999,999.99') -------------------------- $800.00 $1,600.00 $1,250.00 $2,975.00 $1,250.00 $2,850.00 $2,450.00 $3,000.00 $5,000.00 $1,500.00 $1,100.00 $950.00 $3,000.00 $1,300.00

14 rows selected

A mascara 'L999G999D99' representa a mesma quantia monetária, mas agora a forma como o número é apresentado varia com os

valores das variáveis NLS:

alter session set nls_territory='AMERICA';alter session set nls_language='AMERICAN';

select to_char(sal,'L999G999D99') from emp;

alter session set nls_territory='PORTUGAL';alter session set nls_language='PORTUGUESE';

select to_char(sal,'L999G999D99') from emp;

alter session set succeeded.

alter session set succeeded.

TO_CHAR(SAL,'L999G999D99') --------------------------

14/01/13 Funções para conversão de tipos

5/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

$800.00 $1,600.00 $1,250.00 $2,975.00 $1,250.00 $2,850.00 $2,450.00 $3,000.00 $5,000.00 $1,500.00 $1,100.00 $950.00 $3,000.00 $1,300.00

14 rows selected

alter session set succeeded.

alter session set succeeded.

TO_CHAR(SAL,'L999G999D99') -------------------------- ¿800,00 ¿1.600,00 ¿1.250,00 ¿2.975,00 ¿1.250,00 ¿2.850,00 ¿2.450,00 ¿3.000,00 ¿5.000,00 ¿1.500,00 ¿1.100,00 ¿950,00 ¿3.000,00 ¿1.300,00

14 rows selected

Quando a variável NLS_TERRITORY recebe o valor PORTUGAL o símbolo de moeda passa a ser €. Este caracter é muitorecente e nem todos os CHARACTER SET da base de dados o representam, como é o caso da base de dados onde foram

executados estes exemplos;

Nesta página encontra uma descrição mais detalhada deste assunto e uma forma de contornar este problema;

Topo

TO_NUMBER(string)

A função TO_NUMBER converte de cadeia de caracteres para em número.

select empno, ename, job, salfrom empwhere sal> TO_NUMBER('1500');

EMPNO ENAME JOB SAL ---------------------- ---------- --------- ---------------------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000

14/01/13 Funções para conversão de tipos

6/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

7902 FORD ANALYST 3000

7 rows selected

Topo

MASCARA_DATA

A tabela abaixo mostra os valores possíveis para a máscara da data:

FORMATO SIGNIFICADO

SCC ou CC Século; 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo)

YYYY ou SYYYY Século; 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo)

YYY ou YY ou Y Ultimos 3 digitos do ano ou 2 ultimos ou último digito do ano

Y,YYY Ano com separador dos milhares nessa posição

RR Ver descrição abaixo

SYEAR ou YEAR Ano por extenso (em inglês); 'S' faz preceder de '-' as datas 'BC' (Antes de Cristo)

BC ou AD Indicador de BC/AD (Antes de Cristo / Depois de Cristo)

B.C. ou A.D. Indicador de BC/AD com ponto

Q Trimestre do ano

MM Mês em número de 1..12

MONTH Nome do mês preenchido com espaços até 9 caracteres (Setembro)

MON Nome do mês com três letras (JAN, FEB, MAY,...)

WW, IW ou W Semana do ano formato Oracle, semana do ano formato ISO ou semana do mês

DDD ou DD ou D dia do ano, mês ou semana

DAY Nome do dia da semana, por extenso, preenchido com espaços até 9 caracteres

DY Nome do dia da semana com dois caracteres

J Dia do calendário Juliano; o número de dias desde 31 de Dezembro de 4713 A.C.

AM ou PM Indicador de After Moon (AM) ou Post Moon (PM)

A.M.ou P.M. Indicador de After Moon ou Post Moon com pontos

HH ou HH12 Hora do dia em formato 1-12

HH24 Hora do dia em formato 0-23

MI Minutos

SS Segundos

SSSSS Segundos decorridos desde a meia-noite

/.,: Os símbolos de pontuação são reproduzidos no resultado

"string" A string é reproduzida no resultado mas tem que vir entre aspas

FMPrefixo que pode ser acrescentado aos códigos anteriores; utilizado em MONTH ou DAY suprime o

preenchimento de espaços; usado com números suprime os zeros à esquerda

TH, SP, SPTH, THSPSufixos que converte um número em ordinal ou por extenso; usando os dois fica número por extenso

ordinal

Quando a máscara é escrita em maiúsculas, o resultado aparece em maiúsculas. Quando a máscara é escrita em minusculas, o resultado

aparece em minúsculas.

Para mais informação consultar este link

Topo

14/01/13 Funções para conversão de tipos

7/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

O ORACLE e o ano 2000

Se utilizar o elemento de formato de data RR em vez de YY, o século no valor produzido varia de acordo com os dois dígitos que

especificam o ano e os dois digitos do ano actual.

Ano devolvido por SYSDATEValor formulado

(DD-MON-RR)Ano interpretado

1994 27-OUT-95 1995

1994 27-OUT-17 2017

2001 27-OUT-95 1995

2001 27-OUT-17 2017

Como funciona:

Se o ano especificado for

0..49 50..99

Ano Actual 00..49 A data devolvida é do século actual A data devolvida é do século anterior ao actual

Ano Actual 50..99 A data devolvida é do século seguinte A data devolvida é do século actual

Topo

Máscara para Número

O quadro abaixo mostra os formatos que estão disponíveis para números.

Formato Significado Exemplo Resultado

9 Posição numérica - o número de 9s determina a largura de visualização 999999 1234

0 Zeros antes do número 099999 001234

$ Símbolo de moeda americana $999999 $1234

L Símbolo de moeda que varia com NLS_TERRITORY L999999 ?1234

. Usar o ponto como separador das casas décimais 999999.99 1234.00

,Usar a virgula como separador dos milhares (também conhecido por

símbolo de grupo)999,999 1,234

DUsar um separador décimal que varia com NLS_TERRITORY. Nos

países anglo-saxónicos é o ponto. Em Portugal é a virgula999999.99

1234.00 ou

1234,00

GSeparador dos milhares que varia com NLS_TERRITORY. Nos países

anglo-saxónicos é a virgula. Em Portugal é o ponto999,999 1,234 ou 1.234

MI Coloca sinal menos à direita nos valores negativos 999999MI 1234-

PR Coloca números negativos entre parentisis 999999PR (1234)

EEEE Notação científica. O número de 9s depois do ponto define a precisão 9.9999EEEE 1.2340E+03

V Multiplicar por 10n. n=número de 9s depois de V 9999V99 123400

B Converte os nulos em espaços em branco B9999.99 1234.00

FM Remover os espaços em branco à esquerda e direita L999999 ?1234

Quando o número não cabe na máscara o SQL coloca ### em seu lugar.

select to_char(1234,'999') from dual;

14/01/13 Funções para conversão de tipos

8/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

TO_CHAR(1234,'999') ------------------- ####

1 rows selected

Topo

CHR(v1)

Devolve o caracter representado por v1 no código WE8DEC, ISO8859P1 ou outro.

select chr(49) as "CHR(49)", chr(97) as "CHR(97)", chr(38) as "CHR(38)" from dual;

C C C- - -1 a &1 row selected.

Topo

NVL(v1,v2)

Se o valor de v1 for nulo, a função devolve v2.

select sal, comm, sal*14+NVL(comm,0)from emp;

SAL COMM SAL*14+NVL(COMM,0) ---------------------- ---------------------- ---------------------- 800 11200 1600 300 22700 1250 500 18000 2975 41650 1250 1400 18900 2850 39900 2450 34300 3000 42000 5000 70000 1500 0 21000 1100 15400 950 13300 3000 42000 1300 18200

14 rows selected

Topo

GREATEST(v1,v2,v3)

Devolve o maior dos valores v1, v2 ou v3. A função pode receber mais que 3 argumentos.

select greatest(1,2), greatest(1,2,3),

14/01/13 Funções para conversão de tipos

9/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

greatest(1,2,3,4)from dual;

GREATEST(1,2) GREATEST(1,2,3) GREATEST(1,2,3,4) ---------------------- ---------------------- ---------------------- 2 3 4

1 rows selected

Topo

DECODE

A sintaxe do comando DECODE é a seguinte:

DECODE (v, v1, r1 v2, r2 r_default);

Se a expressão v assumir o valor v1 então é colocado o resultado r1, se v2 então r2. Se nenhum desses valores ocorrer então é

colocado r_default. Simula um comando if-then-else ou um comando CASE com sinal de igual (=). O número de pares de valores

(v1,r1) pode variar. O valor r_default é facultativo.

A expressão v pode ser o nome de uma coluna ou o resultado de uma função;

O resultado da expressão v e os valores v1, v2, ... têm que ser do mesmo tipo;

Se r_default for omitido é devolvido null nos casos em que a condição não satisfaça nenhuma das alternativas v1, v2, ....

select grade, decode (grade, 1,'15%', 2,'10%', 3,'8%', '5%') "Bonus"from salgrade;

GRADE Bonus ---------------------- ----- 1 15% 2 10% 3 8% 4 5% 5 5%

5 rows selected

select ename, job, decode(job, 'CLERK', 'Empregado de escritório', 'ANALYST', 'Analista programador', 'MANAGER', 'Gestor', 'SALESMAN', 'Vendedor', 'PRESIDENT', 'Presidente', 'UNKNOWN') "Job2"from emp;

14/01/13 Funções para conversão de tipos

10/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

ENAME JOB Job2 ---------- --------- ----------------------- SMITH CLERK Empregado de escritório ALLEN SALESMAN Vendedor WARD SALESMAN Vendedor JONES MANAGER Gestor MARTIN SALESMAN Vendedor BLAKE MANAGER Gestor CLARK MANAGER Gestor SCOTT ANALYST Analista programador KING PRESIDENT Presidente TURNER SALESMAN Vendedor ADAMS CLERK Empregado de escritório JAMES CLERK Empregado de escritório FORD ANALYST Analista programador MILLER CLERK Empregado de escritório

14 rows selected

Topo

CASE

A instrução CASE é semelhante ao DECODE mas permite comparações com >, >=, <, <=. O exemplo abaixo mostra como substituir

o DECODE do ponto anterior por um CASE:

select ename, job, case when job='CLERK' then 'Empregado de escritório' when job='ANALYST' then 'Analista programador' when job='MANAGER' then 'Gestor' when job='SALESMAN' then 'Vendedor' when job='PRESIDENT' then 'Presidente' else 'Outra função' end as "Função"from emp;

ENAME JOB Função ---------- --------- ----------------------- SMITH CLERK Empregado de escritório ALLEN SALESMAN Vendedor WARD SALESMAN Vendedor JONES MANAGER Gestor MARTIN SALESMAN Vendedor BLAKE MANAGER Gestor CLARK MANAGER Gestor SCOTT ANALYST Analista programador KING PRESIDENT Presidente TURNER SALESMAN Vendedor ADAMS CLERK Empregado de escritório JAMES CLERK Empregado de escritório FORD ANALYST Analista programador MILLER CLERK Empregado de escritório

14 rows selected

A expressão CASE é mais flexivel que DECODE pois suporta outros operadores de comparação além de =:

select sal, case when sal < 800 then 'baixo' when sal < 1500 then 'médio' when sal < 2500 then 'médio alto'

14/01/13 Funções para conversão de tipos

11/11aserlorenzo.com/manSQL/Oracle/dml/funcoes/funcoesconversao.htm

else 'alto' end as Salariofrom emp;

SAL SALARIO---------- ---------- 800 médio 1600 médio alto 1250 médio 2975 alto 1250 médio 2850 alto 2450 médio alto 3000 alto 5000 alto 1500 médio alto 1100 médio 950 médio 3000 alto 1300 médio

14 rows selected

Topo

Realizado por Jose Aser Lorenzo. Última alteração em 2012-05-01