PostgreSQL è un popolare database relazionale open source con una solida reputazione per la sua affidabilità, flessibilità e supporto per gli standard tecnici aperti. Supporta i tipi di dati relazionali e non relazionali, rendendolo uno dei database relazionali più conformi, stabili e maturi.
In questa guida, ci immergeremo in profondità nell'uso di Regex con PostgreSQL.
Prerequisiti:
Per eseguire i passaggi dimostrati in questa guida, sono necessari i seguenti componenti:
Un sistema Linux correttamente configurato. Ulteriori informazioni sulla creazione di una macchina virtuale Ubuntu su VirtualBox.
Un'adeguata installazione di PostgreSQL. Ulteriori informazioni sull'installazione e l'impostazione di PostgreSQL su Ubuntu.
Accesso a un non-radice utente con sudo Ulteriori informazioni sulla gestione dei privilegi sudo usando i sudoers.
Regex e PostgreSQL
A breve termine per espressione regolare, Regex è una stringa di testo che descrive un modello per abbinare, individuare e gestire il testo. È uno strumento versatile è supportato da molti linguaggi di programmazione (Perl, Python, Java, Kotlin, Scala, Go, C ++ ecc.) e strumenti (awk, sed, nginx, grep/egrep e altro).
PostgreSQL utilizza le espressioni regolari di Posix che possono funzionare meglio delle query come COME E SIMILE A operatori.
Database demo
Per scopi dimostrativi, lavoreremo con un database fittizio che è popolato con voci fittizie.
Connettersi al server PostgreSQL locale come Postgres:
$ sudo -i -u postgres psql
Dalla shell PostgreSQL, crea un database fittizio chiamato demo_db:
$ Crea database demo_db;
Passa al nuovo database:
$ \ c demo_db
Crea una nuova tabella:
Crea utenti di tabelle ( Chiave primaria seriale ID, Nome testo non null, Testo e -mail);
Ecco un breve elenco di vari operatori di regex PostgreSQL per la corrispondenza dei motivi:
~: Un operatore comune per abbinare un'espressione regolare (Case Sensitive)
~*: Corrisponde all'espressione regolare data (insensibile caso)
!~: Filtra le occorrenze senza pari (Case Sensitive)
!~*: Filtra le occorrenze senza pari (insensibili casi)
Esempio 1: un'introduzione
Iniziamo il nostro viaggio con l'espressione regolare di PostgreSQL con il seguente esempio:
$ Seleziona * dagli utenti dove email ~ '^.*$ ';
Qui:
L'espressione regolare si apre con l'operatore Tilde (~) seguito da un jolly (*). Seleziona tutti i record dalla tabella degli utenti.
L'operatore "^" indica l'inizio di una stringa.
IL "."L'operatore è un operatore jolly per un singolo carattere.
L'operatore "$" indica la fine di una stringa.
Tutti questi operatori insieme, “^.*$ ", Indica una stringa che inizia con qualsiasi personaggio e finisce con qualsiasi stringa.
Esempio 2: Case Sensibile
Per impostazione predefinita, l'operatore Tilde (~) è sensibile alla maiuscolo. Possiamo dimostrare questa proprietà usando le seguenti domande:
$ Seleziona * da utenti dove email ~ 'esempio';
$ Seleziona * da utenti dove email ~ 'esempio';
Esempio 3: caso insensibile
Se vogliamo eseguire le operazioni di insensibile al caso, dobbiamo includere l'operatore asterisco (*) insieme all'operatore Tilde (~).
$ Seleziona * da utenti dove email ~ * 'esempio';
$ Seleziona * da utenti dove email ~ * 'esempio';
Dal momento che abbiamo specificato l'interpretazione insensibile al caso dell'espressione regolare, entrambe le query restituiscono lo stesso output.
Esempio 4: Invert Match
Per impostazione predefinita, le espressioni regolari corrispondono al modello specificato.
Dai un'occhiata alla seguente domanda:
$ Seleziona * dagli utenti dove e -mail !~ 'Esempio';
$ Seleziona * dagli utenti dove e -mail !~ 'Esempio';
Qui:
Il comportamento predefinito dell'operatore "~" è quello di abbinare il modello specificato da Regex.
Usando il "!~ ”Operatore, ignoriamo le istanze in cui il modello corrisponde.
Per eseguire la corrispondenza invertibile insensibile al caso, usa "!~*"Operatore invece:
$ Seleziona * dagli utenti dove e -mail !~* 'Esempio';
$ Seleziona * dagli utenti dove e -mail !~* 'Esempio';
Esempio 5: filtrare i dati entro la fine della stringa
Esegui la seguente domanda:
$ Seleziona * dagli utenti in cui e -mail ~ * 'com $';
Qui:
Filtriamo le stringhe da E-mail colonna in base all'estremità della coda.
L'operatore "$" indica la fine della stringa.
Il regex "com $" descrive che è abbinata alle stringhe che ha "com" alla fine.
Esempio 6: filtrare i dati entro l'inizio della stringa
Guarda la seguente domanda:
$ Seleziona * dagli utenti in cui e -mail ~ * '^m';
Qui:
Filtriamo le stringhe dalla colonna e -mail in base alle loro iniziali.
L'operatore "^" indica l'inizio di una stringa.
Il regex “^m” descrive che è abbinata alle stringhe che ha “m” all'inizio.
Esempio 7: dati di tipo numerico
Possiamo anche usare l'espressione regolare per specificare le voci di ricerca che contengono cifre. La seguente query lo dimostra perfettamente:
$ Seleziona * dagli utenti in cui e-mail ~ '[0-9]';
In questa query, utilizziamo la funzione Classe di caratteri dell'espressione regolare. Fondamentalmente, è un modo di scegliere tra le classi di personaggi. Ad esempio, [0-9] è una rappresentazione della classe dei caratteri delle cifre.
Per riferimento, ecco un breve elenco di varie classi di carattere di espressione regolari:
Lettera maiuscola: [[:superiore:]] O [A-Z]
Lettera minuscola: [[:inferiore:]] O [A-Z]
Alfabeto: [[:alfa:]] O [A-Za-Z]
Alfanumerico: [[: Alnum:]] O [A-za-Z0-9]
Esadecimale: [[: xdigit:]] O [0-9a-fa-f]
ASCII: [[: ASCII:]]
Funzioni di regex postgresql
Oltre all'espressione regolare di Posix, PostgreSQL include anche varie funzioni specifiche per regex. Questa sezione mostra queste funzioni con esempi.
Regexp_replace ()
La sintassi del Regexp_replace () La funzione è la seguente:
$ Regexp_replace (, , ,)
Qui:
fonte: Una stringa in cui si svolge la sostituzione.
modello: Un'espressione regolare di Posix per abbinare le sotto-corde che dovrebbero essere sostituite.
sostituzione_string: Una stringa per sostituire le corrispondenze del pattern regex.
bandiere: È un componente opzionale. Può essere uno o più carattere che influenza il comportamento della funzione.
Possiamo anche distribuire il Regexp_replace () per lavorare sulle stringhe dalle voci del database:
$ Seleziona regexp_replace (email, '[0-9]', '*', 'g') dagli utenti;
Come indicato, la funzione sostituisce tutte le cifre da tutte le voci nella colonna e -mail con un asterisco (*).
Regexp_matches ()
Come suggerisce il nome della funzione, Regexp_matches () viene utilizzato per trovare tutte le istanze del modello in una determinata stringa. La sintassi della funzione è la seguente:
$ Regexp_matches (, , );
Qui:
fonte: Una stringa in cui la funzione cerca il modello dato.
modello: Uno schema che è descritto nell'espressione regolare di Posix.
bandiere: Può essere uno o più personaggi che influenzano il comportamento della funzione.
Dai un'occhiata al seguente esempio:
$ Seleziona regexp_matches ("The Quick Brown Fox", "The", "G");
Notare che Regexp_matches () Stampa l'output come set.
Successivamente, filtriamo i dati dal nostro database:
$ Seleziona regexp_matches (email, ".+@(.*) $ ') Dagli utenti;
L'output è un insieme di tutti i domini univoci degli indirizzi e -mail.
Substring ()
IL Substring () La funzione restituisce una parte di una determinata stringa. La sottostringa viene generata in base ai parametri dati.
La sintassi del Substring () La funzione è la seguente:
$ Substring (, , )
Qui:
corda: La stringa da cui la funzione genera una sottostringa.
posizione di partenza: Un valore intero positivo che specifica la posizione per estrarre la sottostringa. Se il valore è 0, l'estrazione della sottostringa inizia al primo carattere della stringa.
lunghezza: Un valore intero positivo che specifica il numero di caratteri da estrarre da start_position. Se non fornito, la funzione agisce come se il valore è la lunghezza massima della stringa data.
I seguenti esempi dimostrano il comportamento del Substring () funzione perfettamente:
$ Select substring ("The Quick Brown Fox", 6, 9); $ Select substring ("The Quick Brown Fox", 6);
Possiamo anche utilizzare questa funzione per manipolare i dati estratti dal database:
$ Seleziona sottostringa distinta (e -mail da '.+@(.*) $ ') Dagli utenti;
L'output stampa tutti i domini univoci degli indirizzi e -mail come elenco di sottostringi.
Split_part ()
IL Split_part () La funzione prende una stringa e la divide in parti diverse. La sintassi della funzione è la seguente:
$ Split_part (, , );
Qui:
corda: La stringa che è divisa in più substringhe.
delimitatore: Una stringa che viene utilizzata come delimitatore per la divisione.
posizione: La posizione della parte per tornare. Il valore inizia da 1.
Dai un'occhiata al Split_part () funzione in azione:
$ Select split_part ('a, b, c, d, e', ',', 4);
$ Select split_part ('a, b, c, d, e', ',', 2);
Nel seguente esempio, usiamo il Split_part () Per dividere gli indirizzi e -mail e prendere il dominio:
$ Select split_part (email, '@', 2) dagli utenti;
Conclusione
In questa guida, abbiamo fatto un tutorial approfondito sull'uso dell'espressione regolare per costruire le istruzioni SQL e filtrare i dati su PostgreSQL. L'uso dell'espressione regolare consente molto più flessibilità rispetto agli altri metodi stabiliti. Ad esempio, usando il COME operatore.
Oltre all'implementazione di base, abbiamo anche dimostrato l'uso di Regex in varie altre funzioni PostgreSQL per migliorare notevolmente la loro funzionalità. In effetti, puoi anche implementare l'espressione regolare sulle funzioni definite dall'utente.
Interessato a saperne di più? La sottocategoria PostgreSQL contiene centinaia di guide e tutorial sulle varie caratteristiche di PostgreSQL.
