PostgreSQL er en populær open source relasjonsdatabase med et solid rykte for sin pålitelighet, fleksibilitet og støtte for åpne tekniske standarder. Den støtter både relasjonelle og ikke-relasjonelle datatyper, noe som gjør det til en av de mest kompatible, stabile og modne relasjonsdatabasene.
I denne guiden vil vi dykke dypt inn i å bruke regex med PostgreSql.
Forutsetninger:
For å utføre de demonstrerte trinnene i denne guiden, trenger du følgende komponenter:
Et riktig konfigurert Linux-system. Lær mer om å lage en Ubuntu Virtual Machine på VirtualBox.
En riktig postgreSQL -installasjon. Lær mer om å installere og sette opp PostgreSQL på Ubuntu.
Tilgang til en ikke-rot bruker med sudo Lær mer om å administrere sudo -privilegium ved hjelp av sudoers.
Regex og PostgreSql
En kort sikt for vanlig uttrykk, Regex er en tekststreng som beskriver et mønster for å matche, lokalisere og administrere teksten. Det er et allsidig verktøy støttes av mange programmeringsspråk (Perl, Python, Java, Kotlin, Scala, Go, C ++ osv.) og verktøy (AWK, SED, NGINX, GREP/EGREP og mer).
PostgreSQL bruker Posix sine vanlige uttrykk som kan fungere bedre enn spørsmål som SOM og LIK operatører.
Demo -database
For demonstrasjonsformål skal vi jobbe med en dummy -database som er befolket med dummy oppføringer.
Koble til den lokale PostgreSql -serveren som Postgres:
$ sudo -i -u postgres psql
Fra PostgreSql -skallet, oppretter du en dummy -database som heter demo_db:
$ Opprett database demo_db;
Bytt til den nye databasen:
$ \ c demo_db
Lag et nytt tabell:
Lag tabellbrukere ( ID Seriell primærnøkkel, Navn tekst ikke null, E -posttekst);
Her er en kort liste over forskjellige PostgreSQL Regex -operatører for mønstermatching:
~: En vanlig operatør som samsvarer med et vanlig uttrykk (case sensitiv)
~*: Samsvarer med det gitte vanlige uttrykket (Case Unensitive)
!~: Filtrerer de uovertrufne forekomstene (case sensitive)
!~*: Filtrerer de uovertrufne forekomster (Case Unsensitive)
Eksempel 1: En introduksjon
La oss starte reisen med PostgreSQL regelmessig uttrykk med følgende eksempel:
$ Velg * fra brukere der e -post ~ '^.*$ ';
Her:
Det vanlige uttrykket åpnes med Tilde (~) -operatøren etterfulgt av et jokertegn (*). Den velger alle postene fra brukertabellen.
"^" -Operatøren betegner starten på en streng.
“.”Operatør er en jokertegnoperatør for en enkelt karakter.
"$" -Operatøren betegner slutten av en streng.
Alle disse operatørene sammen, “^.*$ ”, Betegner en streng som starter med enhver karakter og ender med Enhver streng.
Eksempel 2: Sak sensitiv
Som standard er Tilde (~) -operatøren Case Sensitive. Vi kan demonstrere denne egenskapen ved å bruke følgende spørsmål:
$ Velg * fra brukere der e -post ~ 'eksempel';
$ Velg * fra brukere der e -post ~ 'eksempel';
Eksempel 3: Case ufølsom
Hvis vi ønsker å utføre de etterfølgende operasjonene, må vi inkludere asterisk (*) -operatøren sammen med Tilde (~) -operatøren.
$ Velg * fra brukere der e -post ~ * 'Eksempel';
$ Velg * fra brukere der e -post ~ * 'Eksempel';
Siden vi spesifiserte den case-ufølsomme tolkningen av det vanlige uttrykket, returnerer begge spørsmålene samme utgang.
Eksempel 4: Invert Match
Som standard samsvarer vanlige uttrykk for det spesifiserte mønsteret.
Ta en titt på følgende spørsmål:
$ Velg * fra brukere der e -post !~ 'eksempel';
$ Velg * fra brukere der e -post !~ 'Eksempel';
Her:
Standard oppførsel til "~" -operatøren er å samsvare med Regex -spesifiserte mønster.
Bruker "!~ ”Operatør, vi ignorerer forekomstene der mønsteret samsvarer.
For å utføre Case Inensitive Invert-kampen, bruk "!~*”Operatør i stedet:
$ Velg * fra brukere der e -post !~* 'Eksempel';
$ Velg * fra brukere der e -post !~* 'Eksempel';
Eksempel 5: Filtrer dataene mot slutten av strengen
Kjør følgende spørsmål:
$ Velg * fra brukere der e -post ~ * 'com $';
Her:
Vi filtrerer strengene fra E -post kolonne basert på halen.
"$" -Operatøren indikerer slutten av strengen.
Regex “com $” beskriver at det samsvarer med strenger som har “com” på slutten.
Eksempel 6: Filtrer dataene ved begynnelsen av strengen
Se på følgende spørsmål:
$ Velg * fra brukere der e -post ~ * '^m';
Her:
Vi filtrerer strengene fra e -postkolonnen basert på initialene deres.
"^" -Operatøren indikerer begynnelsen på en streng.
Regex “^m” beskriver at det samsvarer med strenger som har “M” i begynnelsen.
Eksempel 7: Data av numerisk type
Vi kan også bruke det vanlige uttrykket for å spesifisere funnoppføringene som inneholder sifre. Følgende spørring demonstrerer det perfekt:
$ Velg * fra brukere der e-post ~ '[0-9]';
I denne spørringen bruker vi karakterklassefunksjonen til vanlig uttrykk. I utgangspunktet er det en snarvei måte å representere visse karakterklasser på. For eksempel er [0-9] en representasjon av sifferklassen.
For referanse er her en kort liste over forskjellige vanlige uttrykkskarakterklasser:
Stor bokstav: [[:øverste:]] eller [A-Z]
Liten bokstav: [[:Nedre:]] eller [A-Z]
Alfabet: [[: alfa:]] eller [A-Za-Z]
Alfanumerisk: [[: Alnum:]] eller [A-ZA-Z0-9]
Heksadesimal: [[: xDigit:]] eller [0-9a-fa-f]
ASCII: [[: ascii:]]
PostgreSQL Regex -funksjoner
Foruten Posix regelmessig uttrykk, kommer PostgreSQL også med forskjellige regex-spesifikke funksjoner. Denne delen viser disse funksjonene med eksempler.
Regexp_replace ()
Syntaksen til Regexp_replace () Funksjonen er som følger:
$ Regexp_replace (, , ,)
Her:
kilde: En streng der erstatningen finner sted.
mønster: Et Posix regelmessig uttrykk for å matche delstrengene som bør erstattes.
erstatning_string: En streng for å erstatte regex -mønsterkampene.
flagg: Det er en valgfri komponent. Det kan være en eller flere karakterer som påvirker oppførselen til funksjonen.
Vi kan også distribuere Regexp_replace () å jobbe med strenger fra databaseoppføringer:
$ Velg regexp_replace (e-post, '[0-9]', '*', 'g') fra brukere;
Som instruert erstatter funksjonen alle sifrene fra alle oppføringene under e -postkolonnen med en stjerne (*).
Regexp_matches ()
Som navnet på funksjonen antyder, Regexp_matches () brukes til å finne alle forekomster av mønsteret i en gitt streng. Funksjonssyntaksen er som følger:
$ Regexp_matches (, , );
Her:
kilde: En streng der funksjonen søker etter det gitte mønsteret.
mønster: Et mønster som er beskrevet i Posix regelmessig uttrykk.
flagg: Det kan være en eller flere tegn som påvirker oppførselen til funksjonen.
Sjekk ut følgende eksempel:
$ SELECT REGEXP_MATCHES ('The Quick Brown Fox', 'The', 'G');
Noter det Regexp_matches () skriver ut utdataene som et sett.
Deretter filtrerer vi dataene fra databasen vår:
$ Velg regexp_matches (e -post, '.+@(.*) $ ') Fra brukere;
Utgangen er et sett med alle de unike domenene til e -postadressene.
Substring ()
De Substring () Funksjonen returnerer en del av en gitt streng. Substring genereres basert på de gitte parametrene.
Syntaksen til Substring () Funksjonen er som følger:
$ Substring (, , )
Her:
streng: Strengen som funksjonen genererer en substring fra.
start_position: En positiv heltallverdi som spesifiserer posisjonen til å trekke ut substring fra. Hvis verdien er 0, starter substring -ekstraksjonen ved den første tegnet av strengen.
lengde: En positiv heltallverdi som spesifiserer antall tegn som skal henter ut fra start_position. Hvis ikke gitt, fungerer funksjonen som om verdien er den maksimale lengden på den gitte strengen.
Følgende eksempler viser oppførselen til Substring () funksjon perfekt:
$ SELECT SUBSTRING ('The Quick Brown Fox', 6, 9); $ SELECT SUBSTRING ('The Quick Brown Fox', 6);
Vi kan også bruke denne funksjonen til å manipulere dataene som trekkes fra databasen:
$ Velg distinkt substring (e -post fra '.+@(.*) $ ') Fra brukere;
Utgangen skriver ut alle de unike domenene til e -postadressene som en liste over underlag.
Split_part ()
De Split_part () Funksjonen tar en streng og deler den i forskjellige deler. Syntaksen til funksjonen er som følger:
$ Split_part (, , );
Her:
streng: Strengen som er delt inn i flere underlag.
avgrensning: En streng som brukes som avgrenser for splitting.
posisjon: Posisjonen til delen til å komme tilbake. Verdien starter fra 1.
Sjekk ut Split_part () Funksjon i handling:
$ Velg split_part ('a, b, c, d, e', ',', 4);
$ Velg split_part ('a, b, c, d, e', ',', 2);
I det følgende eksemplet bruker vi Split_part () For å dele e -postadressene og ta tak i domenet:
$ Velg split_part (e -post, '@', 2) fra brukere;
Konklusjon
I denne guiden gikk vi på en grundig opplæring om å bruke det vanlige uttrykket for å konstruere SQL-setningene og filtrere dataene på PostgreSQL. Bruken av vanlig uttrykk gir langt mer fleksibilitet enn de andre etablerte metodene. For eksempel ved å bruke SOM operatør.
I tillegg til grunnleggende implementering, demonstrerte vi også bruken av REGEX i forskjellige andre PostgreSQL -funksjoner for å forbedre funksjonaliteten deres kraftig. Faktisk kan du også implementere det vanlige uttrykket på brukerdefinerte funksjoner.
Interessert i å lære mer? PostgreSQL-underkategorien inneholder hundrevis av guider og opplæringsprogrammer om de forskjellige funksjonene i PostgreSQL.
