PostgreSQL é um popular banco de dados relacional de código aberto, com uma sólida reputação por sua confiabilidade, flexibilidade e suporte para padrões técnicos abertos. Ele suporta tipos de dados relacionais e não relacionais, tornando-o um dos bancos de dados relacionais mais compatíveis, estáveis e maduros.
Neste guia, mergulharemos profundamente no uso de regex com o PostgreSQL.
Pré -requisitos:
Para executar as etapas demonstradas neste guia, você precisa dos seguintes componentes:
Um sistema Linux adequadamente configurado. Saiba mais sobre como criar uma máquina virtual do Ubuntu no VirtualBox.
Uma instalação postgreSQL adequada. Saiba mais sobre como instalar e configurar o PostgreSQL no Ubuntu.
Acesso a um não-raiz usuário com sudo Saiba mais sobre como gerenciar o privilégio do sudo usando sudoers.
Regex e PostgreSQL
Um curto prazo para expressão regular, Regex é uma série de texto que descreve um padrão para combinar, localizar e gerenciar o texto. É uma ferramenta versátil é suportada por muitas linguagens de programação (Perl, Python, Java, Kotlin, Scala, Go, C ++ etc.) e ferramentas (awk, sed, nginx, grep/egrep e muito mais).
O PostgreSQL usa as expressões regulares do POSIX que podem funcionar melhor do que consultas como COMO e IGUAL A operadores.
Banco de dados de demonstração
Para fins de demonstração, vamos trabalhar com um banco de dados dummy que é preenchido com entradas fictícias.
Conecte -se ao servidor local PostgreSQL como PostGres:
$ sudo -i -u pós -gres psql
A partir do shell postgreSql, crie um banco de dados dummy chamado Demo_DB:
$ Crie banco de dados Demo_db;
Mude para o novo banco de dados:
$ \ c Demo_db
Crie uma nova tabela:
Crie usuários de tabela ( Id Id Serial Primary Key, Nome de texto não nulo, Texto em e -mail);
Aqui está uma pequena lista de vários operadores PostGresql Regex para correspondência de padrões:
~: Um operador comum para combinar com uma expressão regular (sensível ao caso)
~*: Corresponde à expressão regular fornecida (insensível ao caso)
!~: Filtra as ocorrências incomparáveis (sensíveis ao caso)
!~*: Filtra as ocorrências incomparáveis (insensível ao caso)
Exemplo 1: uma introdução
Vamos começar nossa jornada com a expressão regular do PostGresql com o exemplo a seguir:
$ Select * de usuários onde e -mail ~ '^.*$ ';
Aqui:
A expressão regular é aberta com o operador TILDE (~) seguido de um curinga (*). Ele seleciona todos os registros da tabela de usuários.
O operador "^" indica o início de uma string.
O ".”O operador é um operador curinga para um único caractere.
O operador "$" indica o fim de uma string.
Todos esses operadores juntos, “^.*$ ”, Denote uma string que começa com qualquer personagem e termina com qualquer string.
Exemplo 2: Sensível ao caso
Por padrão, o operador TILDE (~) é sensível ao maiúsculas de minúsculas. Podemos demonstrar esta propriedade usando as seguintes consultas:
$ SELECT * dos usuários onde email ~ 'Exemplo';
$ SELECT * dos usuários onde email ~ 'Exemplo';
Exemplo 3: Case insensível
Se quisermos executar as operações insensíveis ao caso, precisamos incluir o operador Asterisk (*) junto com o operador TILDE (~).
$ SELECT * dos usuários onde email ~ * 'Exemplo';
$ SELECT * dos usuários onde email ~ * 'Exemplo';
Como especificamos a interpretação insensível ao caso da expressão regular, ambas as consultas retornam a mesma saída.
Exemplo 4: Invert Match
Por padrão, expressões regulares correspondem ao padrão especificado.
Dê uma olhada na seguinte consulta:
$ Select * de usuários onde e -mail !~ 'exemplo';
$ Select * de usuários onde e -mail !~ 'Exemplo';
Aqui:
O comportamento padrão do operador "~" é corresponder ao padrão REGEX especificado.
Usando o “!~ ”Operador, ignoramos as instâncias em que o padrão corresponde.
Para executar a partida invertida insensível ao caso, use o “!~*”Operador em vez disso:
$ Select * de usuários onde e -mail !~* 'Exemplo';
$ Select * de usuários onde e -mail !~* 'Exemplo';
Exemplo 5: filtre os dados até o final da string
Execute a seguinte consulta:
$ SELECT * dos usuários onde email ~ * 'com $';
Aqui:
Nós filtramos as cordas do E-mail coluna baseada no final da cauda.
O operador "$" indica o fim da string.
O regex "com $" descreve que é correspondente a strings que têm "com" no final.
Exemplo 6: filtre os dados até o início da string
Veja a seguinte consulta:
$ SELECT * dos usuários onde email ~ * '^m';
Aqui:
Filtamos as cordas da coluna de e -mail com base em suas iniciais.
O operador "^" indica o início de uma string.
O regex "^m" descreve que é correspondente a strings que têm "m" no começo.
Exemplo 7: dados do tipo numérico
Também podemos usar a expressão regular para especificar as entradas de localização que contêm dígitos. A consulta a seguir demonstra perfeitamente:
$ Select * de usuários onde email ~ '[0-9]';
Nesta consulta, usamos o recurso de classe de caracteres da expressão regular. Basicamente, é uma maneira de atalho de representar certas classes de personagens. Por exemplo, [0-9] é uma representação da classe de caracteres de dígitos.
Para referência, aqui está uma pequena lista de várias classes regulares de personagens de expressão:
Letra maiúscula: [[:superior:]] ou [A-Z]
Letra minúscula: [[:mais baixo:]] ou [A-Z]
Alfabeto: [[:alfa:]] ou [A-ZA-Z]
Alfanumérico: [[: Alnum:]] ou [A-ZA-Z0-9]
Hexadecimal: [[: xdigit:]] ou [0-9A-FA-F]
ASCII: [[: ASCII:]]
Funções PostGresql Regex
Além da expressão regular do POSIX, o PostgreSQL também vem com várias funções específicas do Regex. Esta seção mostra essas funções com exemplos.
Regexp_replace ()
A sintaxe do Regexp_replace () A função é a seguinte:
$ Regexp_replace (, , ,)
Aqui:
fonte: Uma string onde a substituição ocorre.
padrão: Uma expressão regular POSIX para corresponder às sub-strings que devem ser substituídas.
substituição_string: Uma string para substituir o padrão regex corresponde.
bandeiras: É um componente opcional. Pode ser um ou mais personagens que influenciam o comportamento da função.
Vamos colocar a função em ação:
$ Select regexp_replace ('abc123xyz', '[a-za-z]', ',' g ');
Também podemos implantar o Regexp_replace () Para trabalhar em strings de entradas de banco de dados:
$ Select regexp_replace (email, '[0-9]', '*', 'g') dos usuários;
Conforme instruído, a função substitui todos os dígitos de todas as entradas sob a coluna de email por um asterisco (*).
Regexp_matches ()
Como o nome da função sugere, Regexp_matches () é usado para encontrar todas as instâncias do padrão em uma determinada string. A sintaxe da função é a seguinte:
$ Regexp_matches (, , );
Aqui:
fonte: Uma string em que a função procura o padrão fornecido.
padrão: Um padrão que é descrito na expressão regular do POSIX.
bandeiras: Pode ser um ou mais caracteres que influenciam o comportamento da função.
Confira o seguinte exemplo:
$ Select regexp_matches ('The Quick Brown Fox', 'the', 'g');
Observe que Regexp_matches () imprime a saída como um conjunto.
Em seguida, filtramos os dados do nosso banco de dados:
$ Select regexp_matches (email, '.+@(.*) $ ') Dos usuários;
A saída é um conjunto de todos os domínios exclusivos dos endereços de email.
Substring ()
O Substring () função retorna uma parte de uma determinada string. A substring é gerada com base nos parâmetros fornecidos.
A sintaxe do Substring () A função é a seguinte:
$ Substring (, , )
Aqui:
corda: A string que a função gera uma substring de.
posição inicial: Um valor inteiro positivo que especifica a posição para extrair a substring de. Se o valor for 0, a extração da substring começa no primeiro caractere da string.
comprimento: Um valor inteiro positivo que especifica o número de caracteres para extrair do start_position. Se não for fornecido, a função age como se o valor fosse o comprimento máximo da string especificada.
Os exemplos a seguir demonstram o comportamento do Substring () função perfeitamente:
$ Select Substring ('The Quick Brown Fox', 6, 9); $ Select Substring ('The Quick Brown Fox', 6);
Também podemos utilizar essa função para manipular os dados extraídos do banco de dados:
$ SELECT Substring distinto (email de '.+@(.*) $ ') Dos usuários;
A saída imprime todos os domínios exclusivos dos endereços de email como uma lista de substringas.
Split_part ()
O Split_part () A função leva uma string e a divide em diferentes partes. A sintaxe da função é a seguinte:
$ Split_part (, , );
Aqui:
corda: A string que é dividida em múltiplas substâncias.
delimitador: Uma string usada como delimitador para dividir.
posição: A posição da peça para retornar. O valor começa de 1.
Confira a Split_part () função em ação:
$ Select split_part ('a, b, c, d, e', ',', 4);
$ Select split_part ('a, b, c, d, e', ',', 2);
No exemplo seguinte, usamos o Split_part () Para dividir os endereços de e -mail e pegar o domínio:
$ Select split_part (email, '@', 2) dos usuários;
Conclusão
Neste guia, fizemos um tutorial aprofundado sobre o uso da expressão regular para construir as instruções SQL e filtrar os dados no PostgreSQL. O uso de expressão regular permite muito mais flexibilidade do que os outros métodos estabelecidos. Por exemplo, usando o COMO operador.
Além da implementação básica, também demonstramos o uso do Regex em várias outras funções do PostgreSQL para melhorar bastante sua funcionalidade. De fato, você também pode implementar a expressão regular em funções definidas pelo usuário.
Interessado em aprender mais? A subcategoria PostgreSQL contém centenas de guias e tutoriais sobre os vários recursos do PostgreSQL.
