Nesta publicação, aprenderemos como utilizar a função oracle instr com sua finalidade, sintaxe e exemplos de utilização.

Esta função pode ser de grande utilidade quando se programa em PLSQL no Oracle Database.

O que faz a função INSTR do Oracle / PLSQL ?

A princípio, a função INSTR tem o objetivo de retornar a localização de uma substring em uma outra string.

Parece Complicado ? Mas não é o que aparenta ser, por Exemplo :
Vamos supor que você precisa localizar em um cadastro de clientes, se o nome do cliente possui a palavra “Sanches” no campo nome, ou se você necessita selecionar todas as cidades com a palavra “Santo” na coluna cidade.

Neste caso, a função INSTR do Oracle database poderá ser de grande utilidade como veremos no decorrer deste tutorial.

INSTR : Sintaxe da Função Oracle

A sintaxe para a função INSTR no Oracle / PLSQL é:

INSTR( string, substring [, posição_inicial [, th_aparição ] ] )

Parâmetros ou Argumentos da função INSTR

string

A string para pesquisar. string pode ser CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB ou NCLOB.

substring

A substring a procurar em string . substring pode ser CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB ou NCLOB.

posição_inicial

Opcional. A posição na string onde a pesquisa será iniciada. Se omitido, o padrão é 1. A primeira posição na string é 1. Se a posição_inicial for negativa, a função INSTR contará de volta o número de caracteres de posição_inicial do final da string e então procurará o início da string .

th_aparição

Opcional. A enésima aparição de substring . Se omitido, o padrão é 1.

Retorno da função INSTR

A função INSTR retorna um valor numérico. A primeira posição na string é 1.
Se a substring não for encontrada na string , a função INSTR retornará 0.

Compatibilidade da função Oracle INSTR

A função INSTR pode ser usada nas seguintes versões do Oracle / PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

A seguir veremos exemplos de utilização da função INSTR em ação.

Exemplos de utilização da função Oracle INSTR PLSQL

SELECT INSTR('Aprenda Oracle com o Web Mundi.com', 'com') as PrimeiraOcorrenciadaPalavra FROM dual

Como resultado, temos:

PRIMEIRAOCORRENCIADAPALAVRA
1 16

Neste caso, o Resultado retornou 16, pois é primeira posição da esquerda para a direita onde a substring ‘com’ foi encontrada na string  ‘Aprenda Oracle com o Web Mundi.com’

De maneira idêntica, outra forma de utilização é iniciar a pesquisa da direita para esquerda, assim você pode encontrar a posição da última ocorrência da substring na string informando -1 no parâmetro posição_inicial

SELECT INSTR('Aprenda Oracle com o Web Mundi.com', 'com', -1) UltimaOcorrenciadaPalavra FROM dual

Como resultado, temos:

ULTIMAOCORRENCIADAPALAVRA
1 32

Neste caso, o Resultado retornou 32, pois é primeira posição da direita para esquerda onde a substring ‘com’ foi encontrada na string  ‘Aprenda Oracle com o Web Mundi.com’

Outros exemplos de utilização INSTR Oracle PLSQL

Em seguida, veremos agora outros exemplos de utilização da função INSTR() do Oracle como Localizar na tabela clientes, registros que possuem a palavra “Sanches” em seu nome

Select nome_cliente from tb_clientes_webmundi where instr(nome_cliente, 'Sanches') > 0

Como resultado, temos:

nome_cliente

1 José Carlos Sanches
2 Adalgiza Sanches
3 Fernando da Silva Sanches
4 José Sanches Faria
5 Alessandra Sanches de Oliveira
6 Antonio Sanches
7 João Sanches Silva
8 Alexandre Sanches
9 Alessandra Sanches Alves
10 Alex Sanches Oliveira
11 Ariane Alves Sanches
12 Aline Santos Sanches
13 Osvaldo Sanches
14 Renato Sanches
15 Oscar Sanches Salvador
16 Luiz Sanches Pereira

Mais usos da função INSTR Oracle PLSQL

Em seguida, mais um exemplo da função instr em um select que é selecionar todas as cidades com a palavra “Santo” na coluna nome_cidade.

Select nome_cidade from tb_clientes_webmundi where instr(nome_cidade , 'Santo') > 0

Como resultado, temos:

NOME_CIDADE

1 Santo Anastacio
2 Santo Antonio da Platina
3 Brejo Santo
4 Santo Antonio de Posse
5 Santos
6 Santo Andre
7 Monte Santo de Minas
8 Santo Antonio do Monte
9 Monte Santo
10 Santo Antonio do Jardim
11 Santo Expedito
12 Espirito Santo do Pinhal
13 Santo Antonio do Pinhal
14 Santo Angelo

Acima de tudo, vale lembrar que a string a ser pesquisada é Case-sensitive, ou seja, a string “Santo” é diferente de “santo”, veja o resultado da busca pela string “santo” na nossa base de dados de clientes webmundi.

Select nome_cidade from tb_clientes_webmundi where instr(nome_cidade , 'santo') > 0

Como resultado, temos:

NOME_CIDADE

1 monte santo de minas
2 santo andre
3 santos

Outro exemplo de uso função INSTR Oracle PLSQL

Por fim, uma maneira de você selecionar todas as as cidades que contenham a string  “santo” , independente da string estar no banco com maiúsculo, minúsculo ou somente a primeira maiúscula é utilizar a função instr juntamente com as funções UPPER, LOWER ou INITCAP.

Veja um exemplo abaixo com a função UPPER que converterá a string em maiúscula, note que passo o parâmetro de substring também em maiúscula ‘SANTO’ :

Select nome_cidade from tb_clientes_webmundi where instr(upper(nome_cidade) , 'SANTO') > 0

Como resultado, temos:

NOME_CIDADE

1 Santo Anastacio
2 SANTO ANDRE
3 Santo Antonio da Platina
4 SANTOS
5 Brejo Santo
6 Santo Antonio de Posse
7 monte santo de minas
8 Santos
9 Santo Andre
10 Monte Santo de Minas
11 santo andre
12 Santo Antonio do Monte
13 Monte Santo
14 Santo Antonio do Jardim
15 santos
16 Santo Expedito
17 Espirito Santo do Pinhal
18 Santo Antonio do Pinhal
19 Santo Angelo

Importante: Acima de tudo, note que apesar de ter colocado a função upper em conjunto com a função instr, o resultado da query, retornou nomes de cidade de diferentes formas, como por exemplo a cidade de Santo André. Retornou SANTO ANDRE, Santo Andre e santo andre.

Similarmente, poderia utilizar a função lower que converterá a string em minúscula, note que passo o parâmetro de substring também em minúscula ‘santo’

Select nome_cidade from tb_clientes_webmundi where instr(lower(nome_cidade) , 'santo') > 0

Além disso, uma outra forma é utilizar a função Initcap, esta função define somente o primeiro caractere da String em maiúscula, note que passo o parâmetro de substring também com a Primeira letra em maiúscula ‘Santo’

Select nome_cidade from tb_clientes_webmundi where instr(Initcap(nome_cidade) , 'Santo') > 0

Por fim, você pode conferir mais exemplos de utilização da função INSTR PLSQL no site da própria Oracle em:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm#OLADM564

Dicas de Livros de Oracle

Quer aprender mais sobre Oracle e PL/SQL e não sabe por onde começar?

Para isso, separamos algumas dicas legais de livros sobre Oracle e programação PL/SQL para você aprender ainda mais sobre este poderoso banco de dados, confira:

Amazon.com.br

Materiais e leituras que recomendamos para você:

• Instalação Oracle Database 18c XE Linux
• Instalação SQL Server 2017 Express + SSMS + Adventure Works
• Diferença entre Data Science, Business Intelligence e Big Data
• Business Intelligence em 4 Minutos com Qlikview
• Teclas de Atalho Qlikview

Obrigado

Por último, espero que esta publicação tenha sido útil e lhe ajudado a entender um pouco mais sobre a função INSTR do Oracle PL/SQL.

Em nossa seção de Oracle, você confere outras dicas sobre este banco de dados.

Se você gostou, compartilhe estas dicas com um amigo ou participe de nossas redes sociais.

Conhece outras formas de utilizar a função INSTR em PL/SQL ?

Deixe nos comentários ou envie para nós em nossa página do Facebook.

Será um prazer incluir sua dica neste post com os devidos créditos a você.

Caso queira nos incentivar a criar mais conteúdos como este, prestigie nossos anunciantes.

Um Grande Abraço e até a próxima dica!