Expressões Regulares para uso em Modelagem de Bancos de DadosAs expressões regulares são um grande recurso para ajudar a garantir a integridade das informações, em especial no uso com domínios.
Em ciência da computação, uma expressão regular (ou o estrangeirismo regex, abreviação do inglês regular expression) provê uma forma concisa e flexível de identificar cadeias de caracteres de interesse, como caracteres particulares, palavras ou padrões de caracteres. Expressões regulares são escritas numa linguagem formal que pode ser interpretada por um processador de expressão regular, um programa que ou serve um gerador de analisador sintático ou examina o texto e identifica partes que casam com a especificação dada.
O termo deriva do trabalho do matemático norte-americano Stephen Cole Kleene, que desenvolveu as expressões regulares como uma notação ao que ele chamava de álgebra de conjuntos regulares. Seu trabalho serviu de base para os primeiros algoritmos computacionais de busca, e depois para algumas das mais antigas ferramentas de tratamento de texto da plataforma Unix.
O uso atual de expressões regulares inclui procura e substituição de texto em editores de texto e linguagens de programação, validação de formatos de texto (validação de protocolos ou formatos digitais), realce de sintaxe e filtragem de informação.
(Wikipedia - http://pt.wikipedia.org/wiki/Express%C3%B5es_regulares)
Correspondência com o Padrão e Expressões Regulares no PostgreSQL
O PostgreSQL suporta várias formas de correspondência com o padrão (pattern matching): o tradicional operador SQL LIKE,
o mais recente operador SIMILAR TO (adicionado no SQL 1999) e as expressões regulares estilo POSIX (também implementada na unção substring).
As regex são um recurso muito útil aos DBAs. As expressões regulares oferecem força e agilidade.
LIKE
LIKE - case sensitive
ILIKE - case insensitive
Caracteres Coringa:
% - 0 ou mais caracteres
_ - 1 único caractere
NOT LIKE
~~ = LIKE
~~* = ILIKE
!~~ = NOT LIKE
!~~* = NOT ILIKE
SIMILAR TO
Semelhante ao LIKE mas usa expressões regulares do SQL.
Assim como o LIKE somente é válido quando toda a string corresponde ao padrão.
Em expressões regulares qualquer parte da string pode corresponder ao padrão.
Caracteres coringa:
% - 0 ou mais caracteres
_ - 1 único caractere
Adiciona:
| - ou
* - 0 ou mais vezes
+ - 1 ou mais vezes
() - agrupar itens
[] - similar ao POSIX
POSIX
~ - case sensitive e corresponde
~* - case insensitive e corresponde
!~ - case sensitive e não corresponde
!~* - case insensitive e não corresponde
Átomos do Padrão
(er) - expressão regular. Correspondência para a er
[caracteres] - corresponde a qualquer dos caracteres
\k - caractere não alfanumérico
\c - caractere alfanumérico
. - qualquer único caractere
x - este é o único caractere sem função, representa 'x' mesmo
[:alnum:] Caracteres alfanuméricos, o que no caso de ASCII corresponde a [A-Za-z0-9].
[:alpha:] Caracteres alfabéticos, o que no caso de ASCII corresponde a [A-Za-z].
[:blank:] Espaço e tabulação, o que no caso de ASCII corresponde a [ \t].
[:cntrl:] Caracteres de controle, o que no caso de ASCII corresponde a [\x00-\x1F\x7F].
[:digit:] Dígitos, o que no caso de ASCII corresponde a [0-9]. O Perl oferece o atalho \d.
[:graph:] Caracteres visíveis, o que no caso de ASCII corresponde a [\x21-\x7E].
[:lower:] Caracteres em caixa baixa, o que no caso de ASCII corresponde a [a-z].
[:print:] Caracteres visíveis e espaços, o que no caso de ASCII corresponde a [\x20-\x7E].
[:punct:] Caracteres de pontuação, o que no caso de ASCII corresponde a [-!"#$%&'()*+,./:;<=>?@[\\\]_`{|}~].
[:space:] Caracteres de espaços em branco, o que no caso de ASCII corresponde a [ \t\r\n\v\f]. O Perl oferece o atalho \s, que, entretanto, não é exatamente equivalente; diferente do \s, a classe ainda inclui um tabulador vertical, \x11 do ASCII.[4]
[:upper:] Caracteres em caixa alta, o que no caso de ASCII corresponde a [A-Z].
[:xdigit:] Dígitos hexadecimais, o que no caso de ASCII corresponde a [A-Fa-f0-9].
Expressões regulares não podem terminar com \.
Quantificadores dos caracteres do Padrão:
* - uma seqüência de 0 ou mais correspondências do átomo
+ - uma seqüência de 1 ou mais correspondências do átomo
? - uma seqüência de 0 ou 1 correspondência do átomo
{m} - uma seqüência de exatamente m correspondências do átomo
{m,} - uma seqüência de m ou mais correspondências do átomo
{m,n} - uma seqüência de m a n (inclusive) correspondências do átomo; m não pode ser maior do que n
Caracteres Delimitadores do Padrão
^ - início da string
$ - final da string
Alguns exemplos:
SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
regexp_split_to_table function splits a string using a POSIX regular expression pattern as a delimiter.
The regexp_split_to_array function behaves the same as regexp_split_to_table, except that regexp_split_to_array returns its result as an array of text. It has the syntax regexp_split_to_array(string, pattern [, flags ]). The parameters are the same as for regexp_split_to_table.
SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'\\\s+') AS foo;
foo
--------
the
quick
brown
fox
jumped
over
the
lazy
dog
(9 rows)
SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog', E'\\s+');
regexp_split_to_array
------------------------------------------------
{the,quick,brown,fox,jumped,over,the,lazy,dog}
(1 row)
SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
Example of how to escape "_" in s aimple query
create table foo (str varchar(16));
insert into foo (str) values ('abc.defghi');
insert into foo (str) values ('abc_defghi');
I want to select all strings starting with abc_def
select * from foo where str like E'abc\\_def%';
> Fernando Brombatti wrote:
>> Alguém já usou função para extrair números de uma string?
>>
>> Ex.: AB345CD234 => 345234
>
>
> lista=# select regexp_replace('AB345CD234', '[A-Z]', '','g');
> regexp_replace
> ----------------
> 345234
> (1 row)
>
Dica na lista pgbe-geral:
Complementando a resposta do Shander:
Caso sua string possa conter outros caracteres não numéricos, além das
letras [A-Z], o uso de '[^[:digit:]]' é mais abrangente.
http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
bdteste=# SELECT regexp_replace('AB3,45CD/xz234', '[^[:digit:]]', '',
'g');
regexp_replace
----------------
345234
Osvaldo
Bom tutorial no site:
http://www.oreillynet.com/pub/a/databases/2006/02/02/postgresq_regexes.html
Reprozindo aqui os exemplos do tutorial.
Vamos criar uma tabela:
CREATE DATABASE regex;
CREATE TABLE myrecords(record text);
Insira os registros:
insert into myrecords (record) values
('a'),
('ab'),
('abc'),
('123abc'),
('132abc'),
('123ABC'),
('abc123'),
('4567'),
('5678'),
('6789');
Consultas simples usam o operador ~ (til) seguido por uma string e retornam somente os que atendem ao case.
SELECT record FROM myrecords WHERE record ~ '1'; -- Retornaram todos os registros que contenham '1'.
SELECT record FROM myrecords WHERE record ~ 'a';
SELECT record FROM myrecords WHERE record ~ 'A';
SELECT record FROM myrecords WHERE record ~ '3a';
Para retornar sem olhar o case usamos ~*:
SELECT record FROM myrecords WHERE record ~* 'a';
SELECT record FROM myrecords WHERE record ~* '3a';
Agora não trazendo o que contém a string e sensível ao case !~:
SELECT record FROM myrecords WHERE record !~ '1';
Trazendo sem olhar o case e não trazendo onde tem a string !~*:
SELECT record FROM myrecords WHERE record !~* 'c';
Trazendo as strings que comecem com um certo caractere (^):
SELECT record FROM myrecords WHERE record ~ '^1';
SELECT record FROM myrecords WHERE record ~ '^a';
SELECT record FROM myrecords WHERE record ~* '^a';
Terminados com ($):
SELECT record FROM myrecords WHERE record ~ 'c$';
SELECT record FROM myrecords WHERE record ~ 'bc$';
SELECT record FROM myrecords WHERE record ~* 'bc$';
Veja agora algumas consultas e analise seus resultados:
SELECT record FROM myrecords WHERE record ~ '[a]'; -- Qualquer que tenha a
SELECT record FROM myrecords WHERE record ~ '[A]'; -- Qualquer que tenha A
SELECT record FROM myrecords WHERE record ~* '[a]'; -- Qualquer que tenha a ou A
SELECT record FROM myrecords WHERE record ~ '[ac]'; -- Qualquer que tenha a ou c
SELECT record FROM myrecords WHERE record ~ '[ac7]'; -- Qualquer que tenha a ou c ou 7
SELECT record FROM myrecords WHERE record ~ '[a7A]'; -- Qualquer que tenha a ou 7 ou A
SELECT record FROM myrecords WHERE record ~* '[ac7]'; -- Qualquer que tenha a ou c ou 7 sem olhar o case
SELECT record FROM myrecords WHERE record ~ '[z]';
SELECT record FROM myrecords WHERE record ~ '[z7]';
SELECT record FROM myrecords WHERE record !~ '[4a]';
Procurar por uma faixa de valores:
SELECT record FROM myrecords WHERE record ~ '[1-4]';
Outros de faixa:
SELECT record FROM myrecords WHERE record ~ '[a-c5]';
SELECT record FROM myrecords WHERE record ~* '[a-c5]';
SELECT record FROM myrecords WHERE record ~ '[a-cA-C5-7]'; -- 3 faixas, a-c, A-C e 5-7
Correspondendo 2 ou mais caracteres:
SELECT record FROM myrecords WHERE record ~ '3[a]';
SELECT record FROM myrecords WHERE record ~ '[3][a]';
SELECT record FROM myrecords WHERE record ~ '[1-3]3[a]';
SELECT record FROM myrecords WHERE record ~ '[23][a]';
SELECT record FROM myrecords WHERE record ~ '[2-3][a]';
SELECT record FROM myrecords WHERE record ~ '[a-b][b-c]';
Nesta ordem:
SELECT record FROM myrecords WHERE record ~ '[a][c]';
Iniciando com dígitos:
SELECT record FROM myrecords WHERE record ~ '^[0-9]$';
Fazendo escolhas (ou |):
SELECT record FROM myrecords WHERE record ~ '^a|c$';
Começando com a ou 5 ou terminando com c:
SELECT record FROM myrecords WHERE record ~ '^a|c$|^5';
SELECT record FROM myrecords WHERE record ~ '[^0-9|^a-z]';
Repetindo Caracteres:
SELECT record FROM myrecords WHERE record ~ 'a*'; -- 0 ou mais
SELECT record FROM myrecords WHERE record ~ 'b+'; -- 1 ou mais
SELECT record FROM myrecords WHERE record ~ 'a?'; -- 0 ou 1
SELECT record FROM myrecords WHERE record ~ '[0-9]{3}'; -- Exatamente uma quantidade, usar {#}
SELECT record FROM myrecords WHERE record ~ '[0-9]{4,}'; -- Exatamente ou mais, usar {#,}
SELECT record FROM myrecords WHERE record ~ '[a-c0-9]{2,3}'; -- Exatamente de 2 até 3, inclusive
Exemplos com a função Substring:
CREATE TABLE log(record text);
Inserir registros:
insert into log (record) values
('a'),
('ab'),
('abc'),
('123abc'),
('132abc'),
('123ABC'),
('abc123'),
('4567'),
('5678'),
('6789');
SELECT substring(record, '[a-zA-Z0-9:. ]{1,}') FROM log LIMIT 1;
SELECT date(substring(record, '[a-zA-Z ]{1,}[0-9]{1,}') || ' 2005') AS "Date" FROM log LIMIT 1;
SELECT substring('Nov 3 07:37:51 localhost', '[:0-9]{2,}') AS "Time";
SELECT substring('Nov 30 07:37:51 localhost', '[:0-9]{2,}') AS "Time";
SELECT substring('Nov 30 07:37:51 localhost', '[:0-9]{3,}') AS "Time";
SELECT substring(record, '[:0-9]{3,}') AS "Time" FROM log LIMIT 1;
SELECT substring(record, 'SRC=*([.0-9]{2,})') AS "IP Address" FROM log LIMIT 1;
SELECT substring(record, 'SPT=*([.0-9]{2,})') AS "Remote Source Port"
FROM log LIMIT 1;
SELECT substring(record, 'DPT=*([.0-9]{2,})') AS "Destination Port"
FROM log LIMIT 1;
O SQL completo:
SELECT
date(substring(record, '[a-zA-Z ]{1,}[0-9]{1,}') || ' 2005') AS "Date",
substring(record, '[:0-9]{3,}') AS "Time",
substring(record, 'SRC=*([.0-9]{2,})') AS "Remote IP Address",
substring(record, 'SPT=*([.0-9]{2,})') AS "Remote Source Port",
substring(record, 'DPT=*([.0-9]{2,})') AS "Destination Port"
FROM log;
Mais detalhes no tutorial e na documentação do PostgreSQL:
http://pgdocptbr.sourceforge.net/pg80/functions-matching.html
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html
|
