Curso Oracle 11g - Lesson 7 - Programação Part2

--Lesson 7
--Usando expressoes regulares

--REGEXP_LIKE
--usando duas condições no like
select first_name, last_name
from employees
where regexp_like (first_name, '^Ste(v|ph)en$');

--REGEXP_REPLACE
--Substitui todos os caracteres correspondentes por outro
select phone_number, regexp_replace(phone_number, '\.','-') as phone, --substitui todos que econtrar
regexp_replace(phone_number, '^6','-') as phone2, --substitui o primeiro correspondente que encontar
regexp_replace(phone_number, '$4','-') as phone3, --substitui o último correspondente que encontar
regexp_replace(phone_number, '(5|3|1)','-') as phone4 --todos correspondetes
from employees;

--REGEXP_INSTR
--acha a primeira posição do caracter alpha
SELECT street_address,
REGEXP_INSTR(street_address,'[[:alpha:]]') AS First_Alpha_Position
FROM  locations;

--REGEXP_SUBSTR
--entra um determinado caracter e traz o restante
SELECT street_address,
REGEXP_SUBSTR(street_address , ' [^ ]+ ') AS Road --procura o espaço em branco e traz a palavra até o próximo espaço em branco
                                                  -- o sina de " + " é o indicador para repetir a expressão
FROM locations;              


--Subexpressions
--exemplo procurar uma seguencia de DNA
SELECT REGEXP_INSTR('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag',     
                    '(gtc(tcac)(aaag))',
                     1, 1, 0, 'i',
                     1) "Position"
FROM dual;


--REGEXP_COUNT
--quantas vezes aparece uma determinada expressão
SELECT REGEXP_COUNT( 'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag', 'gtc') AS Count
FROM dual;              


--Check Constraints
ALTER TABLE employees
  ADD CONSTRAINT email_addr   
  CHECK(REGEXP_LIKE(email,'@')) NOVALIDATE;

INSERT INTO employees VALUES  
  (500,'Christian','Patel','ChrisP2creme.com',    
   1234567890,'12-Jan-2004','HR_REP',2000,null,102,40);

select * from employees;



Fiquem a vontade para comentar e sugerir melhorias e/ou correção dos exemplos acima.