Jak wyciąć ze stringa wszystkie literki i znaki specjalne?

Dane, które w naszej aplikacji są kluczowe: służą za element identyfikacyjny naszych rekordów np numer telefonu czy numer NIP czy też często występują w warunkach wyszukiwania np. nazwisko klienta - powinny mieć ustandaryzowany format w bazie danych. Dzięki temu nie będziemy się musieli zastanawiać, czy w numerze telefonu cyfry mamy rozdzielane myślnikami czy spacjami, czy nazwisko klienta jest napisane wielkimi literami czy małymi.

Format kluczowych danych powinien być ustalony jeszcze na etapie analizy a najpóźniej na etapie projektu technicznego. Zapis tych danych powinien się odbywać specjalizowaną funkcją (API). Daje nam to gwarancję, że wszystkie dane zapisane do bazy będą spełniały ustalony format.

Przy rozwoju naszych aplikacji często jednak pracujemy na danych pozyskanych z różnych źródeł i nie mamy wpływu na ich jakość.

Kłopoty mogą sprawiać dane, których format przechowywania w bazie nie jest ustandaryzowany i dane zapisywane są dokładnie w ten sposób, w jaki wprowadził je przez GUI użytkownik. 

Cyfry w numerach telefonu czy NIP mogą być rozdzielone myślnikami, kropkami czy nawet gwiazdkami. NIP może zawierać literowe oznaczenie kraju lub nie.  Imię i nazwisko może być raz pisane małymi, raz wielkimi literami.

Sposobów wprowadzania tych wartości mogą być setki - zależy to wyłącznie od wyobraźni użytkownika!!!

Jeśli chcemy ustandaryzować nasze dane i pozbyć się z tekstu nadmiarowych znaków specjalnych czy usunąć literki możemy to zrobić za pomocą SQL lub PLSQL  w bardzo prosty sposób: wystarczy skorzystać z funkcji TRANSLATE i REPLACE.

TRANSLATE(TEKST, PARAM1, PARAM2)

zamienia znaki wskazane w parametrze1 na odpowiednie znaki wskazane w parametrze2

REPLACE(TEKST, param1, param2)

zamienia ciąg znaków wskazany w param1 na ciag znaków w parametrze2

Możemy wykorzystać te dwie funkcję by wyczyściła nam podejrzane dane:

TRANSLATE -zamienia nam wybrane znaki na spacje.

W kroku pierwszym zamienimy niechciane przez nas znaki czyli literki oraz znaki specjalne takie jak .*,/ itp na spacje.

REPLACE - usuwa nam spacje

W kroku drugim - usuniemy wszystkie spacje.

I proszę - nasze dane ustandaryzowane!

  select  

  replace(translate(upper(:tekst1),'ABCDEFGHUIJKLMNOPRSTUWVXY.-*/',' '),' ') as wytnij_literki  

  from dual;  

Dzięki pilnowaniu spójnych formatów danych unikniemy także kłopotów wydajnościowych. Dzięki temu, że będziemy wiedzieć, że format naszych danych jest spójny dla wszystkich rekordów- np wszystkie NAZWISKA zawsze przechowywane są w bazie pisane wielkimi literami - przy wyszukiwaniu rekordu po NAZWISKU  nie będziemy musieli korzystać z funkcji typu UPPER czy LOWER by porównywać stringi. A to daje nam możliwość zakładania indeksów na tych kolumnach. 

Zakładanie indeksów na kolumnach, które nie mają ustalonych standardów nie ma większego sensu gdyż w takich wypadkach trzeba w warunkach WHERE używać funkcji konwertujących typu  UPPER LOWER czy innych a to uniemożliwia nam skorzystanie z indeksu na tabeli.