Trabalhando com Strings no Oracle
03/06/2009
Vou agora tentar mostrar como trabalha com string no Oracle… Venho acompanhando alguns Foruns de Desenvolvimento e sempre estou me deparando com questões sobre o correto uso de funções para trabalhar com String…
- Concatenando 2 Strings
- Colocando a primeira letra do texto como Maiúscula
- Transformando o texto em texto Minúsculo e Maiúsculo
- Convertendo letras em códigos ASCII
- Localizando a posição uma string no meio do texto
- Atribuindo dígitos no final e/ou no inicio do texto
- Removendo espaços no inicio e/ou fim do texto
- Como inverter o texto
- Substituindo Strings por outras Strings
- Cortando o conteúdo do texto
- Descobrindo o tamanho da string
- Retornando colunas com valor não nulo
- Exemplo da utilização de algumas funções
- Pesquisando em uma string por um digito e exibindo o resto da string pegando como base esse digito localizado.
- Repartindo uma string e inserindo um valor no meio.
1. Concatenando 2 strings
Existem duas formas de trabalhar concatenando strings. CONCAT e PIPE
Concat:
SQL: SELECT CONCAT ('Rodrigo ', 'Valentim') FROM dual;
CONCAT('RODRIGO','VALENTIM')
----------------------------
Rodrigo Valentim
Usando PIPE:
SQL: SELECT 'Rodrigo '||'Valentim' FROM dual; 'RODRIGO'||'VALENTIM' --------------------- Rodrigo Valentim
2. Colocando a primeira letra do texto como Maiúscula.
SQL: SELECT INITCAP('rodrigo valentim') FROM dual;
INITCAP('RODRIGOVALENTIM')
--------------------------
Rodrigo Valentim
3. Alternar entre Maiúscula e Minúscula
SQL: SELECT LOWER('RODRIGO ')||UPPER('valentim') FROM dual;
LOWER('RODRIGO')||UPPER('VALEN
------------------------------
rodrigo VALENTIM
4. Convertendo letras em códigos ASCII
SQL: SELECT ASCII('A') FROM dual;
ASCII('A')
----------
65
SQL: SELECT ASCII('Z') FROM dual;
ASCII('Z')
----------
90
SQL: SELECT ASCII('a') FROM dual;
ASCII('A')
----------
97
SQL: SELECT ASCII('z') FROM dual;
ASCII('Z')
----------
122
SQL: SELECT ASCII(' ') FROM dual;
ASCII('')
----------
32
5. Localizando a posição uma string no meio do texto
Com a função INSTR, é possível localizar caracteres dentro de um texto, de forma normal ou inversa.
Localizando a primeira posição da string no texto
SQL: SELECT INSTR('Exemplo de como realizar pesquisa em uma string', 'r', 1, 1) localiza_1 from dual;
LOCALIZA_1
----------
17
Localizando a segunda posição da string no texto
SQL: SELECT INSTR('Exemplo de como realizar pesquisa em uma string', 'r', 1, 2) localiza_2 from dual;
LOCALIZA_2
----------
24
Localizando a terceira posição da string no texto
SQL: SELECT INSTR('Exemplo de como realizar pesquisa em uma string', 'r', 1, 3) localiza_3 from dual;
LOCALIZA_3
----------
44
Localizando a quarta posição da string no texto, como não existe mais de 3 letras r no texto, ele retorna zero.
SQL: SELECT INSTR('Exemplo de como realizar pesquisa em uma string', 'r', 1, 4) localiza_4 from dual;
LOCALIZA_4
----------
0
Para realizar a mesma pesquisa so que de ordem inversa, basta usar o valor negativo na função
SQL: SELECT INSTR('Exemplo de como realizar pesquisa em uma string', 'r', -1, 1) localiza_1 from dual;
LOCALIZA_1
----------
44
Nestas condições, ele me retorna a primeira posição na ordem inversa, ou seja, a última.
6. Atribuindo dígitos no final e/ou no inicio do texto.
Temos como usar o LPAD para inserir digitos na esquerda e RPAD para inserir digitos na direita.
LPAD:
SQL: SELECT LPAD('Rodrigo Valentim', 25, 'x') FROM dual;
LPAD('RODRIGOVALENTIM',25,'X')
------------------------------
xxxxxxxxxRodrigo Valentim
RPAD:
SQL: SELECT RPAD('Rodrigo Valentim', 25, 'x') FROM dual;
RPAD('RODRIGOVALENTIM',25,'X')
------------------------------
Rodrigo Valentimxxxxxxxxx
7.Removendo dígitos do inicio ou do fim ou de ambos do texto
Podendo ser espaços em branco ou letras (TRIM só remove espaços em branco).
LTRIM:
SQL: SELECT '*' || LTRIM(' Rodrigo Valentim ') || '*' FROM dual;
'*'||LTRIM('RODRIGOVALENTIM')|
---------------------------------
*Rodrigo Valentim *
RTRIM:
SQL: SELECT '*' || RTRIM(' Rodrigo Valentim ') || '*' FROM dual;
'*'||RTRIM('RODRIGOVALENTIM')|
---------------------------------
* Rodrigo Valentim*
TRIM:
SQL: SELECT '*'||TRIM(' Rodrigo Valentim ')||'*' FROM dual;
'*'||TRIM('RODRIGOVALENTIM')||
------------------------------
*Rodrigo Valentim*
8. Como inverter o conteúdo em uma string
SQL: select reverse('MITNELAV OGIRDOR') from dual;
REVERSE('MITNELAVOGIRDOR')
--------------------------
RODRIGO VALENTIM
9. Substituindo Strings por outras Strings
Com a Função Replace é possível substituir o conjunto de caracteres por outro, no exemplo abaixo, ele vai alterar o valor DE para A
SQL: SELECT REPLACE('ABCDE','DE','A') FROM dual;
REPLACE('ABCDE','DE','A')
-------------------------
ABCA
10. Cortando conteúdo do texto com SUBSTR
Com essa função, é possível exibir somente partes do texto. No exemplo abaixo, digo que quero iniciar meu texto da posição 3 e andar mais 9 dígitos.
SQL: SELECT SUBSTR('Rodrigo Valentim', 3, 9) FIRST_FOUR FROM dual;
FIRST_FOUR
----------
drigo Val
11. Descobrindo o tamanho da string com a função LENGTH.
SQL: SELECT LENGTH('Rodrigo Valentim') FROM dual;
LENGTH('RODRIGOVALENTIM')
-------------------------
16
12. Retornando colunas com valor não nulo
Com a função COALESCE. Ela pesquisa entre as colunas passada e retorna o primeiro valor não nulo que foi encontrado.
SQL: create table artigo_coalesce(col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10)); Table created
SQL: insert into artigo_coalesce values (NULL, NULL, 'C','D');
1 row inserted
SQL: insert into artigo_coalesce values (NULL, 'A', 'C','D');
1 row inserted
SQL: insert into artigo_coalesce values ('B', 'A', 'C','D');
1 row inserted
SQL: insert into artigo_coalesce values (NULL, 'D', NULL,'A');
1 row inserted
SQL: SELECT COALESCE(COL1, COL2, COL3, COL4) FROM artigo_coalesce;
COALESCE(COL1,COL2,COL3,COL4)
-----------------------------
C
A
B
D
13.1 Pesquisando em uma string por um digito e exibindo o resto
Pesquisando em uma string por um digito e exibindo o resto da string pegando como base esse digito localizado. Bom, primeiro preciso saber qual a posição que preciso começar, neste caso, vou iniciar pela ‘\’. Vou pesquisar do fim para o inicio e localizar neste caso, a localização da ultima barra.
SQL: select INSTR('c:\arquivo\arquivo.txt', '\', -1, 1) from dual;
INSTR('C:\ARQUIVO\ARQUIVO.TXT'
------------------------------
11
Depois de localizar, preciso então usar ela como ponto inicial e ir até o final da string completa. Como não sei o tamanho total da minha string, pois, pode ser que ela mude algum dia, vou usar a função LENGTH que me retornará o tamanho total.
SQL: SELECT substr('c:\arquivo\arquivo.txt',INSTR('c:\arquivo\arquivo.txt', '\', -1, 1) ,length('c:\arquivo\arquivo.txt')) from dual;
SUBSTR('C:\ARQUIVO\ARQUIVO.TXT
------------------------------
\arquivo.txt
Depois de localizar o inicio e fim da minha string através das funções acima, preciso ajustar para que a barra não saia… a função SUBSTR está iniciando da posição 11 e indo até a posição final da string, por isso está me retornando a barra, então, preciso somar + 1 ao retorno da localização da ‘\’, para então ela não aparecer no meu resutlado.
SQL: SELECT substr('c:\arquivo\arquivo.txt',INSTR('c:\arquivo\arquivo.txt', '\', -1, 1) + 1 ,length('c:\arquivo\arquivo.txt')) from dual;
SUBSTR('C:\ARQUIVO\ARQUIVO.TXT
------------------------------
arquivo.txt
Então, andando da posição 12 até a ultima posição, consigo através do substr cortar parte do texto e extrair somente o que desejo.
13.2 Pegar uma String e inserir um valor no meio dela
Eu tenho uma String chamada ‘Existe Perfeita?’ e quero inserir o Lógica no meio dela utilizando uma query…
Pegando trechos do ultimo exemplo (13.1) vamos montar nossa query e assim ter nosso retorno…
Primeiro preciso pegar o espaço em branco, levando em consideração que é o meio da string.
SQL: SELECT instr('Existe Perfeita?',' ') FROM dual;
INSTR('EXISTEPERFEITA?','')
---------------------------
7
Agora vamos cortar somente o inicio da string, com SUBSTR vamos pegar da posição 1 até o espaço em branco (posição 7)
SQL: SELECT substr('Existe Perfeita?',1,instr('Existe Perfeita?',' ')) FROM dual;
SUBSTR('EXISTEPERFEITA?',1,INS
------------------------------
Existe
Temos então somente o Existe. Vamos então inserir (concatenar) o texto que queremos e ter como resultado o Existe Lógica
SQL: SELECT substr('Existe Perfeita?',1,instr('Existe Perfeita?',' '))||'Lógica' FROM dual;
SUBSTR('EXISTEPERFEITA?',1,INS
------------------------------
Existe Lógica
Agora falta inserir a parte final… vamos então pegar a String inicial e então cortar a parte do meio até o fim. Já sei qual o meio e para descobrir o fim é só usar a função LENGTH e então temos o resultado que queremos.
SQL: SELECT substr('Existe Perfeita?',1,instr('Existe Perfeita?',' '))||'Logica'||substr('Existe Perfeita?',instr('Existe Perfeita?',' '),length('Existe Perfeita?')) "Existe?" FROM dual;
Existe?
-----------------------
Existe Logica Perfeita?
Acredito que tenha conseguido passar um pouco do que é trabalhar com Strings no Oracle…
Espero ter esclarecido algumas dúvidas e as que não foram esclarecidas, podem me procurar que eu vou tentar ajudar!
Abraço e até a próxima.
Rodrigo Valentim é Analista de Sistemas Oracle Developer
http://www.rodrigovalentim.com/blog
