SQL-stringfuncties: een gids voor beginners

Werken met relationele databases betekent vaak dat je met rommelige data te maken krijgt. Een van de meest voorkomende uitdagingen is het opschonen en verwerken van tekstgegevens. Gelukkig biedt SQL krachtige stringfuncties die dit proces veel efficiënter maken, zodat je tijd en moeite bespaart.

In dit artikel bekijken we de belangrijkste SQL-stringfuncties die je helpen om tekstgegevens eenvoudig op te schonen en te manipuleren. Laten we beginnen!

Wat zijn SQL-stringfuncties?

SQL-stringfuncties zijn ingebouwde functies waarmee we tekstgegevens die in een database zijn opgeslagen kunnen manipuleren en verwerken. Veelvoorkomende voorbeelden van tekstvelden zijn namen, beschrijvingen en adressen.

Met deze functies kun je een breed scala aan taken uitvoeren, waaronder:

• Strings samenvoegen
• Tekst opmaken
• Delen van een string extraheren
• Zoeken en specifieke tekst binnen een string vervangen

Strings samenvoegen in SQL

We kunnen twee of meer strings samenvoegen met de functie concat():

SELECT CONCAT('Street Roma',', ','72',', ','Padova') AS full_addressMogelijk gemaakt door

Dit is de output:

full_address           |
-----------------------+
Street Roma, 72, Padova|Mogelijk gemaakt door

Deze functie wordt vaak gebruikt om informatie uit verschillende kolommen te combineren, zoals namen en adressen.

Als alternatief kun je ook de functie CONCAT_WS() gebruiken:

SELECT CONCAT_WS(',','Street Roma','72','Padova') as full_addressMogelijk gemaakt door

Aan de syntaxis zie je dat dit makkelijker en efficiënter is in situaties waarin je meer dan twee kolommen met dezelfde scheidingsteken wilt combineren.

Tekst opmaken

Er zijn verschillende SQL-stringfuncties om tekst aan te passen door de lettergrootte te wijzigen, extra spaties te verwijderen en andere bewerkingen. Elke functie staat hieronder met de syntaxis en de bijbehorende beschrijving.

Laten we nu enkele voorbeelden bekijken om te begrijpen hoe je deze functies toepast. Stel dat je e-mailadressen met hoofdletters en een landcode in kleine letters hebt, en je het formaat van deze kolommen wilt corrigeren.

SELECT
		LOWER('[email protected]') AS email,
		UPPER('it') AS countryMogelijk gemaakt door

Dit is de bijbehorende output:

email              |country|
-------------------+-------+
anonymous@gmail.com|IT     |Mogelijk gemaakt door

Naast het wijzigen van hoofd-/kleine letters is het ook handig om spaties aan het begin of einde van strings te verwijderen. De primaire functie voor dit soort bewerkingen is TRIM() om aan beide kanten te verwijderen. Andere alternatieven zijn LTRIM() en RTRIM() om respectievelijk links en rechts spaties te verwijderen:

SELECT  TRIM(' Street Roma, 72 ') AS trimmed_address,
	  LTRIM(' Street Roma, 72 ') AS ltrimmed_address,
	  RTRIM(' Street Roma, 72 ') AS rtrimmed_addressMogelijk gemaakt door

De query geeft het volgende resultaat terug:

trimmed_address|ltrimmed_address|rtrimmed_address|
---------------+----------------+----------------+
Street Roma, 72|Street Roma, 72 | Street Roma, 72|Mogelijk gemaakt door

Uit de output kun je de verschillen tussen elke functie opmerken. Bovendien kan TRIM() ook worden toegepast om andere speciale tekens te verwijderen, naast spaties. Stel dat we het telefoonveld willen opschonen door het +‑teken te verwijderen:

SELECT TRIM('++345','+') AS telephoneMogelijk gemaakt door

De output is als volgt:

telephone|
---------+
345      |Mogelijk gemaakt door

Zoals je ziet is het telefoonnummer volledig opgeschoond.

Tekst extraheren

Na het samenvoegen en aanpassen van het formaat van strings is het tijd om te ontdekken hoe je tekst extraheert met de speciale SQL-functie SUBSTRING(). Laten we verschillende voorbeelden bekijken om deze functie onder de knie te krijgen:

SELECT
		SUBSTRING('Antony',1,1) AS first_character, 
		SUBSTRING('Antony',1,3) AS first_3characters,
		SUBSTRING('Antony',1,5) AS first_5charactersMogelijk gemaakt door

Dit is de output:

first_character|first_3characters|first_5characters|
---------------+-----------------+-----------------+
A              |Ant              |Anton            |Mogelijk gemaakt door

We kunnen deze functie ook combineren met de eerdere functies om het eerste teken te kapitaliseren, de rest van de letters naar kleine letters om te zetten en vervolgens alle letters te concatenaten:

SELECT
LENGTH('antony') AS lenght_name,
SUBSTRING(UPPER('antony'),1,1) AS first_character,
SUBSTRING(LOWER('antony'),2,LENGTH('antony')) AS  last_characters,
CONCAT(SUBSTRING(UPPER('antony'),1,1),SUBSTRING(LOWER('antony'),
      2,LENGTH('antony'))) AS nameMogelijk gemaakt door

Deze query geeft het volgende resultaat:

lenght_name|first_character|LAST_characters|name  |
-----------+---------------+---------------+------+
          6|A              |ntony          |Antony|Mogelijk gemaakt door

Naast de functies die we tot nu toe hebben gezien, zie je ook de functie LENGTH(), die helpt om de lengte van de string op te halen. In deze context is die handig om de index van het laatste teken te bepalen.

Zoeken en vervangen van strings in SQL

We laten de belangrijkste functies zien om te zoeken en te vervangen:

Een zeer handige functie is REPLACE() om een substring binnen de string door een andere substring te vervangen:

SELECT
		'Street Roma - 32' AS address,
		replace('Street Roma - 32','-',',') AS cleaned_addressMogelijk gemaakt door

Dit is de output:

address         |cleaned_address |
----------------+----------------+
Street Roma - 32|Street Roma , 32|Mogelijk gemaakt door

In dit geval hebben we simpelweg een streepje vervangen door een komma binnen het adres. We kunnen ook de positie van het streepje binnen het adres proberen te vinden:

SELECT
		'Street Roma - 32' AS address,
		LENGTH('Street Roma - 32') AS length_address,
		CHARINDEX('-','Street Roma - 32') AS location_dashMogelijk gemaakt door

De query geeft het volgende resultaat terug:

address         |length_address|location_dash|
----------------+--------------+-------------+
Street Roma - 32|            16|           13|Mogelijk gemaakt door

Je ziet dat het teken - op positie 13 in het adres staat, grotendeels aan het einde van de string.

Conclusie

Het beheersen van deze SQL-stringfuncties kan een groot verschil maken bij het efficiënt opschonen van data. Natuurlijk behandelt dit artikel niet alle functies, maar slechts een korte lijst met de belangrijkste. Niet alle SQL-functies zijn altijd overal geldig en kunnen verschillen per type database dat je gebruikt. Zo kan CHARINDEX in een MySQL-database worden vervangen door LOCATE().

Om met deze functies te oefenen, kun je onze Reporting in SQL-cursus bekijken. Die helpt je een expert te worden in het opschonen van data en het bouwen van complexe rapporten. We raden ook de SQL Fundamentals skill track aan om alle essentiële aspecten van SQL te behandelen.