Co to jest SUBSTR?

SUBSTR (lub SUBSTRING) to funkcja SQL, która służy do wyodrębniania podciągu znaków z ciągu tekstowego. W Google BigQuery, SUBSTR działa na danych typu STRING i BYTES. Funkcja ta pozwala na wyodrębnienie części ciągu tekstowego, określając pozycję początkową i długość podciągu.

Zastosowanie SUBSTR

Funkcja SUBSTR ma szerokie zastosowanie w BigQuery, w tym:

• Wyodrębnianie części danych: SUBSTR pozwala na wyodrębnianie konkretnych fragmentów danych, takich jak numery telefonów, kody pocztowe, nazwy domen, czy części adresów URL.
• Tworzenie nowych kolumn: SUBSTR może być używany do tworzenia nowych kolumn na podstawie istniejących kolumn tekstowych, np. do wyodrębniania nazwisk z pełnych nazw.
• Formatowanie danych: SUBSTR może być używany do formatowania danych tekstowych, np. do dodawania spacji lub usunięcia zbędnych znaków.
• Walidacja danych: SUBSTR można wykorzystać do walidacji danych tekstowych, np. do sprawdzenia, czy ciąg znaków zawiera określony prefiks lub sufiks.
• Analiza danych: SUBSTR może być używany do analizy danych tekstowych, np. do zliczania wystąpień określonych słów lub fraz w tekście.

Przykład użycia w BigQuery

Załóżmy, że mamy tabelę o nazwie „klienci” z kolumną „imię_i_nazwisko” zawierającą pełne imię i nazwisko klienta.

Chcemy wyodrębnić nazwiska klientów z kolumny „imię_i_nazwisko”. Możemy to zrobić za pomocą funkcji SUBSTR:

SELECT
    imię_i_nazwisko,
    SUBSTR(imię_i_nazwisko, INSTR(imię_i_nazwisko, ' ') + 1) AS nazwisko
FROM
    `projekt.dataset.klienci`

W tym przykładzie:

• SUBSTR(imię_i_nazwisko, INSTR(imię_i_nazwisko, ' ') + 1) wyodrębnia podciąg znaków z kolumny „imię_i_nazwisko”, zaczynając od pozycji po pierwszym wystąpieniu spacji (czyli od nazwiska).
• INSTR(imię_i_nazwisko, ' ') zwraca pozycję pierwszego wystąpienia spacji w ciągu tekstowym.
• + 1 przesuwa pozycję początkową o jeden znak, aby rozpocząć wyodrębnianie od początku nazwiska.

Wynik tego zapytania będzie zawierał dwie kolumny: „imię_i_nazwisko” i „nazwisko”. Kolumna „nazwisko” będzie zawierała tylko nazwiska klientów.

Najczęstsze błędy i sposoby ich unikania

Najczęstsze błędy popełniane przy użyciu SUBSTR w BigQuery to:

• Błędna pozycja początkowa: Jeśli pozycja początkowa jest mniejsza od 1 lub większa od długości ciągu tekstowego, funkcja SUBSTR zwróci błąd.
• Błędna długość podciągu: Jeśli długość podciągu jest większa od długości ciągu tekstowego, funkcja SUBSTR zwróci błąd.
• Nieprawidłowe dane wejściowe: Funkcja SUBSTR oczekuje danych typu STRING lub BYTES. Podanie danych innego typu spowoduje błąd.

Aby uniknąć tych błędów, należy:

• Upewnić się, że pozycja początkowa jest poprawna i znajduje się w granicach długości ciągu tekstowego.
• Upewnić się, że długość podciągu jest poprawna i nie przekracza długości ciągu tekstowego.
• Upewnić się, że dane wejściowe są typu STRING lub BYTES.

Optymalizacje i najlepsze praktyki

Aby zoptymalizować zapytania z użyciem SUBSTR w BigQuery, należy:

• Unikać zbędnych operacji: Jeśli możliwe, należy unikać wielokrotnego używania funkcji SUBSTR na tym samym ciągu tekstowym. Zamiast tego, można użyć funkcji REGEXP_EXTRACT lub SPLIT, które mogą być bardziej efektywne.
• Wykorzystanie indeksów: Jeśli dane tekstowe są często filtrowane lub sortowane, warto rozważyć dodanie indeksu do kolumny tekstowej. Indeksy mogą przyspieszyć wyszukiwanie i sortowanie danych.
• Użycie funkcji SAFE_SUBSTR: Funkcja SAFE_SUBSTR jest podobna do funkcji SUBSTR, ale zamiast zwracać błąd w przypadku nieprawidłowych danych wejściowych, zwraca wartość NULL. To może być przydatne w przypadku, gdy dane wejściowe są niepewne.

Porównanie z innymi dialektami SQL

Funkcja SUBSTR jest dostępna w większości dialektów SQL, w tym MySQL, PostgreSQL i Oracle. W większości przypadków, sposób użycia funkcji SUBSTR jest podobny w różnych dialektach. Jednakże, mogą istnieć niewielkie różnice w składni lub zachowaniu.

Na przykład, w MySQL, funkcja SUBSTR może być używana z trzema argumentami lub dwoma. W przypadku użycia dwóch argumentów, drugi argument określa pozycję początkową, a trzeci argument jest pomijany, co oznacza, że funkcja SUBSTR zwróci podciąg od pozycji początkowej do końca ciągu tekstowego.

W BigQuery, funkcja SUBSTR zawsze wymaga trzech argumentów. Jeśli chcesz wyodrębnić podciąg od pozycji początkowej do końca ciągu tekstowego, musisz podać długość podciągu jako trzeci argument, który jest równy długości ciągu tekstowego minus pozycja początkowa.