PostgreSQL to popularna relacyjna baza danych open source z solidną reputacją ze względu na niezawodność, elastyczność i wsparcie dla otwartego standardów technicznych. Obsługuje zarówno relacyjne, jak i nierelacyjne typy danych, co czyni go jedną z najbardziej zgodnych, stabilnych i dojrzałych relacyjnych baz danych.
W tym przewodniku zanurzymy się głęboko w użyciu Regex z PostgreSQL.
Wymagania wstępne:
Aby wykonać wykazane kroki w tym przewodniku, potrzebujesz następujących elementów:
Odpowiednio skonfigurowany system Linux. Dowiedz się więcej o tworzeniu wirtualnej maszyny Ubuntu na VirtualBox.
Właściwa instalacja PostgreSQL. Dowiedz się więcej o instalacji i konfiguracji PostgreSQL na Ubuntu.
Dostęp do nie-źródło użytkownik z sudo Dowiedz się więcej o zarządzaniu przywilejem sudo za pomocą sudoers.
Regex i PostgreSQL
Krótkoterminowy Wyrażenie regularne, Regex to ciąg tekstu, który opisuje wzór dopasowywania, zlokalizowania i zarządzania tekstem. To wszechstronne narzędzie jest obsługiwane przez wiele języków programowania (Perl, Python, Java, Kotlin, Scala, Go, C ++ itp.) i Narzędzia (AWK, SED, Nginx, Grep/Egrep i więcej).
PostgreSQL używa wyrażeń regularnych Posix, które mogą funkcjonować lepiej niż zapytania, takie jak TAK JAK I PODOBNY DO operatorzy.
Baza danych demo
Do celów demonstracyjnych będziemy pracować z fikcyjną bazą danych, która jest wypełniona manekinami.
Połącz się z lokalnym serwerem PostgreSQL jako Postgres:
$ sudo -i -u postgres psql
Z powłoki PostgreSQL utwórz manekinową bazę danych o nazwie demo_db:
$ Utwórz bazę danych demo_db;
Przełącz się na nową bazę danych:
$ \ c demo_db
Utwórz nową tabelę:
Utwórz użytkowników tabeli ( ID szeregowy klucz podstawowy, Tekst nazwy nie null, Tekst e -mail);
Oto krótka lista różnych operatorów regex PostgreSQL do dopasowywania wzorów:
~: Wspólny operator dopasowany do wyrażenia regularnego (wrażliwy na przypadek)
~*: Odpowiada danej ekspresji regularnej (nieczuły przypadek)
!~: Filtruje niezrównane zdarzenia (wrażliwe na sprawę)
!~*: Filtruje niezrównane zdarzenia (niewrażliwe na sprawę)
Przykład 1: Wprowadzenie
Zacznijmy naszą podróż od wyrażenia regularnego PostgreSQL z następującym przykładem:
$ Wybierz * od użytkowników, w których e -mail ~ '^.*$ ';
Tutaj:
Wyrażenie regularne rozpoczyna się operatorem Tylde (~), a następnie wieloznaczną kartą (*). Wybiera wszystkie rekordy z tabeli użytkowników.
Operator „^” oznacza początek ciągu.
„„.”Operator jest operatorem wieloznacznym dla jednego znaku.
Operator „$” oznacza koniec łańcucha.
Wszyscy ci operatorzy razem: „^.*$ ”, Oznacza ciąg, który zaczyna się od Każda postać i kończy się dowolny ciąg.
Przykład 2: wrażliwy na sprawę
Domyślnie operator Tilde (~) jest wrażliwy. Możemy zademonstrować tę właściwość za pomocą następujących zapytań:
$ Select * od użytkowników, w których e -mail ~ „przykład”;
$ Select * od użytkowników, w których e -mail ~ „przykład”;
Przykład 3: przypadek nieczuły
Jeśli chcemy wykonać operacje wrażliwe na przypadek, musimy uwzględnić operatora gwiazdki (*) wraz z operatorem Tilde (~).
$ Select * od użytkowników, w których e -mail ~ * „przykład”;
$ Select * od użytkowników, w których e -mail ~ * „przykład”;
Ponieważ określiliśmy interpretację wyrażenia regularnego, oba zapytania zwracają to samo wyjście.
Przykład 4: Odwróć dopasowanie
Domyślnie wyrażenia regularne pasują do określonego wzoru.
Spójrz na następujące zapytanie:
$ Wybierz * od użytkowników, gdzie e -mail !~ „przykład”;
$ Wybierz * od użytkowników, gdzie e -mail !~ „Przykład”;
Tutaj:
Domyślne zachowanie operatora „~” jest zgodne z wzorem określonym regularnym.
Używając "!~ ”Operator ignorujemy przypadki, w których wzór pasuje.
Aby wykonać dopasowanie odwrócone wrażliwe na przypadek, użyj „!~*”Zamiast tego operator:
$ Wybierz * od użytkowników, gdzie e -mail !~* „Przykład”;
$ Wybierz * od użytkowników, gdzie e -mail !~* „Przykład”;
Przykład 5: Filtruj dane do końca ciągu
Uruchom następujące zapytanie:
$ Wybierz * od użytkowników, w których e -mail ~ * 'com $';
Tutaj:
Filtrujemy struny z E-mail Kolumna oparta na końcu ogona.
Operator „$” wskazuje koniec łańcucha.
Regex „com $” opisuje, że pasuje do strun, które ma „com” na końcu.
Przykład 6: Filtruj dane na początku ciągu
Spójrz na następujące zapytanie:
$ Select * od użytkowników, w których e -mail ~ * '^m';
Tutaj:
Filtrujemy struny z kolumny e -mail na podstawie ich inicjałów.
Operator „^” wskazuje początek ciągu.
Regex „^m” opisuje, że pasuje do strun z „m” na początku.
Przykład 7: Dane typu numerycznego
Możemy również użyć wyrażenia regularnego, aby określić wpisy do znajdowania zawierające cyfry. Poniższe zapytanie idealnie pokazuje:
$ Select * od użytkowników, gdzie e-mail ~ '[0-9]';
W tym zapytaniu używamy klasy postaci wyrażenia regularnego. Zasadniczo jest to skrót od reprezentowania niektórych klas postaci. Na przykład [0-9] jest reprezentacją klasy znaków cyfrowych.
Dla odniesienia, oto krótka lista różnych klas znaków wyrażenia regularnego:
Wielka litera: [[:górny:]] Lub [A-Z]
Mała litera: [[:niżej:]] Lub [A-Z]
Alfabet: [[:alfa:]] Lub [A-za-z]
Alfanumeryczny: [[: alnum:]] Lub [A-ZA-Z0-9]
Szesnastkowy: [[: xdigit:]] Lub [0-9a-fa-f]
ASCII: [[: ASCII:]]
Funkcje Regex PostgreSQL
Oprócz wyrażenia regularnego POSIX, PostgreSQL jest również wyposażony w różne funkcje specyficzne dla regularności. W tej sekcji pokazuje te funkcje z przykładami.
Regexp_replace ()
Składnia Regexp_replace () Funkcja jest następująca:
$ Regexp_replace (, , ,)
Tutaj:
źródło: Ciąg, w którym ma miejsce zamiennik.
wzór: Wyrażenie regularne POSIX pasujące do podprinów, które należy wymienić.
flagi: To opcjonalny komponent. Może to być jeden lub więcej postaci, która wpływa na zachowanie funkcji.
Umieśćmy funkcję w działaniu:
$ Wybierz RegexP_replace („ABC123XYZ”, „[a-za-z]”, „,„ g ”);
Możemy również wdrożyć Regexp_replace () Aby pracować nad strunami z wpisów bazy danych:
$ Wybierz RegexP_replace (e-mail, „[0-9]”, „*”, „g”) od użytkowników;
Zgodnie z instrukcją funkcja zastępuje wszystkie cyfry ze wszystkich wpisów w kolumnie e -mail z gwiazdką (*).
RegexP_Matches ()
Jak sugeruje nazwa funkcji, RegexP_Matches () służy do znalezienia wszystkich wystąpień wzoru w danym ciągu. Składnia funkcji jest następująca:
$ Regexp_matches (, , );
Tutaj:
źródło: Ciąg, w którym funkcja wyszukuje dany wzór.
wzór: Wzór opisany w wyrażeniu regularnym POSIX.
flagi: Może to być jeden lub więcej znaków, które wpływają na zachowanie funkcji.
Sprawdź następujący przykład:
$ Wybierz RegexP_Matches („The Quick Brown Fox”, „The”, „G”);
Zauważ, że RegexP_Matches () Wydrukuje wyjście jako zestaw.
Następnie filtrujemy dane z naszej bazy danych:
$ Wybierz RegexP_Matches (e -mail, '.+@(.*) $ ') Od użytkowników;
Wyjście to zestaw wszystkich unikalnych domen adresów e -mail.
Substring ()
Substring () Funkcja zwraca część danego ciągu. Podłoże jest generowane na podstawie podanych parametrów.
Składnia Substring () Funkcja jest następująca:
$ Substring (, , )
Tutaj:
strunowy: Ciąg, z którego funkcja generuje podłoże.
pozycja startowa: Dodatnia wartość liczby całkowitej, która określa pozycję do wyodrębnienia podrzędnego. Jeśli wartość wynosi 0, ekstrakcja substring zaczyna się od pierwszego znaku ciągu.
długość: Dodatnia wartość liczb całkowita, która określa liczbę znaków do wyodrębnienia z pozycji start_position. Jeśli nie jest dostarczona, funkcja działa tak, jakby wartość jest maksymalną długością podanego ciągu.
Poniższe przykłady pokazują zachowanie Substring () Funkcjonuj idealnie:
$ Select Substring („The Quick Brown Fox”, 6, 9); $ Select Substring („The Quick Brown Fox”, 6);
Możemy również wykorzystać tę funkcję do manipulowania danymi pobieranymi z bazy danych:
$ Wybierz odrębny podłoże (e -mail z '.+@(.*) $ ') Od użytkowników;
Wyjście drukuje wszystkie unikalne domeny adresów e -mail jako listę podrzędnych.
Split_part ()
Split_part () Funkcja bierze ciąg i dzieli ją na różne części. Składnia funkcji jest następująca:
$ Split_part (, , );
Tutaj:
strunowy: Ciąg podzielony na wiele podłoża.
SYMITER: Ciąg, który jest używany jako ogranicznik do podziału.
pozycja: Pozycja części do powrotu. Wartość zaczyna się od 1.
Sprawdź Split_part () Funkcja w akcji:
$ Wybierz split_part ('a, b, c, d, e', ',', 4);
$ Wybierz split_part ('a, b, c, d, e', ',', 2);
W poniższym przykładzie używamy Split_part () Aby podzielić adresy e -mail i pobrać domenę:
$ Wybierz split_part (e -mail, „@”, 2) od użytkowników;
Wniosek
W tym przewodniku poszliśmy na dogłębny samouczek na temat używania wyrażenia regularnego do konstruowania instrukcji SQL i filtrowania danych na PostgreSQL. Wykorzystanie ekspresji regularnej umożliwia znacznie większą elastyczność niż inne ustalone metody. Na przykład za pomocą TAK JAK operator.
Oprócz podstawowej implementacji zademonstrowaliśmy również wykorzystanie regularności w różnych innych funkcjach postgreSQL, aby znacznie zwiększyć ich funkcjonalność. W rzeczywistości możesz również zaimplementować wyrażenie regularne na funkcjach zdefiniowanych przez użytkownika.
Zainteresowany nauką więcej? Podkategoria PostgreSQL zawiera setki przewodników i samouczków na temat różnych funkcji PostgreSQL.
