Data opschonen met SQL

Wie kent het niet? Tabellen, bestaande uit kolommen en rijen, waarin eindeloze combinaties van cijfers, letters en leestekens staan. Namen, adressen, telefoonnummers, bedragen, aantallen, data, tijden, etc. vormen complexe databases, waaruit wij informatie kunnen halen.

Toch wordt er niet altijd bij stilgestaan dat ingevoerde data inconsistent kunnen zijn. Namen die met en zonder hoofdletter worden geschreven, adressen met en zonder koppeltekens en wat deden wij ook alweer met tussenvoegsels? Daarom is het bij het bevragen van je database belangrijk om ten eerste goed in je achterhoofd te houden dat deze onregelmatigheden erin zitten. Ten tweede moet je bedenken hoe je de data uniform kunt terugkrijgen.

Strings uniformeren met TRANSLATE

Met de SQL-functie translate() kunnen wij elk karakter in een string vervangen met een ander karakter. Dit klinkt misschien nog wat omslachtig, maar het geeft ons de mogelijkheid om strings uniform te maken. Iedere SQL-variant heeft een translate()-functie, maar nu kijken wij naar die van PostgreSQL. Deze ziet er als volgt uit:

De functie bestaat uit drie argumenten:

In het voorbeeld van vandaag kijken wij naar een tabel uit een database van een sportclub. In deze tabel, genaamd ‘Members’ staat informatie over de aangesloten clubleden. Hierin staan ook de telefoonnummers van deze leden. Zoals je kunt zien, zijn deze nummers op verschillende manieren ingevoerd. Dit maakt de tabel inconsistent. Hierdoor is het lastig om informatie op te vragen. Bovendien is de tabel slecht leesbaar.

Met translate() kunnen wij alle telefoonnummers uniform formatteren. Een functie kunnen wij op verschillende manieren toepassen in SQL, maar voor nu kijken wij enkel naar een manier om een opgeschoonde lijst terug te krijgen, zonder dat wij hiermee de inherente data veranderen. Hierbij stellen wij de voorwaarde dat de telefoonnummers aan elkaar, zonder andere leestekens of spaties worden geformatteerd.

Hiervoor gebruiken wij het SELECT-statement:

Wij krijgen dan het volgende resultaat:

Dit ziet er een stuk beter uit en maakt het zoeken naar specifieke telefoonnummers een stuk gemakkelijker!

Wijzigingen permanent doorvoeren

Nu hebben wij alleen een tijdelijke lijst gemaakt door middel van het SELECT-statement, maar het kan natuurlijk ook wenselijk zijn om deze aanpassing permanent door te voeren in onze tabel. Dit kunnen wij doen met behulp van het UPDATE-statement:

De tabel is nu permanent aangepast met de nieuw geformatteerde telefoonnummers.

Zoals je ziet, is SQL enorm handig om databases op te schonen. Wil je nog meer handige toepassingen van SQL leren? Volg dan onze cursus SQL Fundamentals!