SQL LIKE patroonherkenning: een praktische gids met voorbeelden

Stel, je hebt een tabel employees en je wilt alle namen vinden die beginnen met ‘A’:

Je zou door de tabel kunnen bladeren. Maar waarom zou je, als je de operator LIKE hebt?

SELECT DISTINCT
	first_name
FROM employees
WHERE first_name LIKE 'A%'

De magie zit hier in de clausule `WHERE first_name LIKE ‘A%’`, wat betekent: “vind alle first_name die beginnen met "A" en eindigen met een willekeurig aantal tekens.” De A% hier is een zogeheten patroon voor matching. 

De % is niet de enige wildcard die je in combinatie met de operator LIKE kunt gebruiken. Je kunt ook het underscoreteken _ gebruiken:

• `%` komt overeen met elk aantal tekens.
• _ komt overeen met precies één teken.

De syntaxis is makkelijk te onthouden. Het is simpelweg:

column_name LIKE pattern

Je kunt LIKE gebruiken om allerlei vormen van patroonherkenning te bereiken. Zo doe je dat:

Beginner SQL LIKE-voorbeelden

Hieronder hebben we wat praktische voorbeelden uiteengezet van hoe je de LIKE-instructie kunt gebruiken en de resultaten uit onze voorbeelddataset. 

1. Gebruik LIKE voor een exacte tekenreeks-match

Wil je een exacte tekenreeks-match uitvoeren, gebruik dan LIKE zonder ‘%’ of ‘_’

SELECT
    first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’

2. Gebruik ‘%’ om elk aantal tekens te matchen 

‘%’ kan gebruikt worden om elk (zelfs nul) aantal tekens te matchen – een cijfer, een letter of een symbool.

Stel dat je alle medewerkers wilt vinden van wie de naam begint met ‘Adam’; dan kun je het patroon ‘Adam%’ gebruiken

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Adam%'

Om namen te vinden die eindigen op "z" probeer je het patroon ‘%z’. Je kunt ook meerdere ‘%’ in één patroon gebruiken. Wil je bijvoorbeeld namen vinden die "z" bevatten, gebruik dan ‘%z%’.

3. Gebruik ‘_’ om één (en slechts één) teken te matchen

Net als bij het spel Galgje kan het underscoreteken _ slechts één teken vertegenwoordigen.

Op hoeveel manieren kun je Le_n spellen? Het patroon matcht alles van "Lexn", "LeAn", "Le3n" tot "Le-n".

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Le_n'

Wat zijn de verschillende namen met slechts drie tekens? Dat kunnen we achterhalen met drie opeenvolgende underscores ___ als patroon.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '___'

4. Gebruik zowel ‘%’ als ‘_’ om elk patroon te matchen

Natuurlijk kun je zowel ‘%’ als ‘_’ gebruiken om interessante patronen te maken. 

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '%ann_'

Het patroon ‘%ann_’ matcht een tekenreeks die begint met een willekeurig aantal tekens en eindigt op "ann" en nog één ander teken.

5. Gebruik NOT om tekenreeksen te vinden die niet aan een patroon voldoen

Wat als je alle rijen wilt vinden die niet aan een specifiek criterium voldoen? Dan kun je de operator NOT LIKE gebruiken. Om bijvoorbeeld alle titels te vinden behalve "Staff", kunnen we de syntaxis gebruiken

`WHERE title NOT LIKE ‘Staff’`

Dit is exact gelijk aan de syntaxis

WHERE title != 'Staff'

SELECT DISTINCT
    title
FROM titles
WHERE title NOT LIKE 'Staff'

Natuurlijk kun je NOT LIKE gebruiken met elk van de patronen die we beschreven.

SELECT DISTINCT
    title
FROM titles 
WHERE title NOT LIKE '%engineer'

6. Gebruik LOWER (of UPPER) met LIKE voor case-insensitieve patroonherkenning

Je kunt de volgende syntaxis gebruiken als je patroonherkenning wilt uitvoeren, maar niet zeker weet of de tekenreeks in kleine letters, hoofdletters of gemengd is opgeslagen.

LOWER(column_name) LIKE pattern

De functie LOWER() zet alle tekenreeksen om naar kleine letters, ongeacht of ze als hoofd-, kleine of gemengde letters zijn opgeslagen. 

Let er bij het gebruik van de syntaxis op dat je het patroon volledig in kleine letters schrijft! Anders krijg je mogelijk geen matches.

Je kunt LOWER in de bovenstaande syntaxis ook vervangen door UPPER. Schrijf het patroon dan in hoofdletters.

`UPPER(column_name) LIKE PATTERN`

Wil je bijvoorbeeld weten of de naam van een medewerker "Joanne", "JoAnne", "Joanna" of "JoAnna" is, probeer dan een van de volgende:

SELECT DISTINCT
    first_name
FROM employees
WHERE LOWER(first_name) LIKE 'joann_'

SELECT DISTINCT
    first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'

7. SQL LIKE met meerdere waarden met OR/AND

Je kunt ook meerdere voorwaarden combineren met de LIKE-syntaxis. 

Gebruik bijvoorbeeld de voorwaarde OR om resultaten te vinden die aan minstens één van meerdere LIKE-patronen voldoen.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'

Wil je daarentegen een tekenreeks vinden die aan meerdere LIKE-voorwaarden voldoet, gebruik dan het sleutelwoord AND.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'

De LIKE-syntaxis kan op meerdere kolommen worden toegepast zolang hun variabeletype een tekenreeks met variabele lengte is (varchar). Met die kennis kunnen we de namen vinden van medewerkers met de initialen "Z. Z."

SELECT DISTINCT
    first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'

8. Gebruik LIKE in de SELECT CASE WHEN-clausule 

Tot nu toe hebben we ons gericht op het gebruik van LIKE als voorwaarde om records te selecteren in de WHERE-clausule. We gebruiken LIKE ook in de SELECT-clausule. Kunnen we bijvoorbeeld achterhalen hoeveel medewerkers met de naam "Adam" in onze database staan?

SELECT
    COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees

En hoeveel medewerkers hebben de initialen "A.Z."?

SELECT
    COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees

Veelgebruikte patronen in SQL LIKE

Hier is een samenvatting van de patronen die we hebben besproken als snelle referentie:

Gevorderde voorbeelden van SQL LIKE

De functie LIKE is grotendeels vergelijkbaar in verschillende SQL-smaken (bijv. PostgreSQL, MySQL, Redshift, enz.). Sommige hebben echter extra varianten van de LIKE-functie die het vermelden waard zijn. 

1. De ILIKE-operator

Beschikbaar in Redshift en PostgreSQL, ILIKE is de case-insensitieve variant van LIKE. Daardoor zijn de volgende allemaal equivalent.

SELECT
  datacamp ILIKE ‘datacamp’, -- returns TRUE
  DATACAMP ILIKE ‘datacamp’, -- returns TRUE
  Datacamp ILIKE ‘datacamp’, -- returns TRUE
  datacamp ILIKE ‘DataCamp’, -- returns TRUE

2. Vierkante haken [] en [^] gebruiken als wildcard-tekens

Gebruikers van T-SQL of SQL Server hebben extra wildcard-tekens voor complexere patroonherkenning.

De syntaxis met vierkante haken [] matcht elk specifiek teken binnen het bereik of de set. De volgende voorbeelden geven bijvoorbeeld allemaal TRUE terug.

SELECT
  ‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
  ‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
  ‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
  ‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range

De dakje-in-vierkante-haak [^] wildcard matcht elk enkel teken dat niet binnen het opgegeven bereik of de set valt. Zie je waarom de volgende resultaten zo zijn? 

SELECT
  ‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE 
  ‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE

Hier, omdat "C" binnen het bereik van [C-K] valt, zal het patroon ‘C’ niet overeenkomen met [^C-K]. Dus "Carson" komt niet overeen met [^C-K]arson, maar "Parson" wel.

3. De RLIKE-operator

Beschikbaar in MySQL, de operator RLIKE herkent reguliere expressies (RegEx) in het patroon. RegEx is een krachtig en veelzijdig hulpmiddel voor geavanceerde patroonherkenning. 

Het kan geen kwaad om een basisbegrip van RegEx te hebben, vooral als jouw SQL-smaak RLIKE ondersteunt. Je kunt meer leren over RegEx met onze cursus Regular Expressions in Python.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'

4. De ‘~~’-operator

In PostgreSQL is ‘~~’ volledig synoniem aan LIKE. Er zijn ook equivalenten van ILIKE, NOT LIKE en NOT ILIKE.

Probleemoplossing: veelvoorkomende fouten en misstappen met LIKE

Hier zijn wat veelvoorkomende fouten waar je tegenaan kunt lopen bij het gebruik van LIKE, en hoe je ze corrigeert:

1. Geen resultaten: Controleer op hoofdlettergevoeligheid. In sommige SQL-dialecten is LIKE standaard hoofdlettergevoelig. Gebruik LOWER() of ILIKE (indien ondersteund) voor case-insensitieve matching.
2. Onverwachte resultaten met wildcards: Zorg dat % en _ correct worden gebruikt. % matcht elk aantal tekens, terwijl _ exact één teken matcht. Verkeerd plaatsen kan tot onverwachte matches leiden.
3. Prestatieproblemen: Als je query traag is, let op voorloop-% in patronen (bijv. %pattern), wat indexgebruik uitschakelt. Herschrijf patronen om waar mogelijk met een specifieke string te beginnen. Zie de sectie hieronder voor meer over performance. 
4. SQL-injectiefouten: Als je gebruikersinvoer in je query gebruikt, zorg dan dat die juist geparametriseerd is om SQL-injectie-kwetsbaarheden te voorkomen.
5. Speciale tekens in patronen: Wildcards zoals % en _ in de zoekstring moeten ge-escaped worden als je ze letterlijk bedoelt. Gebruik ESCAPE in je query om een escape-teken te definiëren.

WHERE column_name LIKE '50\% OFF' ESCAPE '\'

Prestatie-overwegingen bij gebruik van de LIKE-operator

De operator LIKE is geweldig, maar kan de query-prestaties beïnvloeden, zeker bij gebruik op grote datasets. Hier zijn enkele aandachtspunten om het gebruik te optimaliseren:

1. Indexen: De operator LIKE presteert het best als het patroon begint met een constante string, zoals Adam%, omdat de database indexen kan gebruiken. Patronen als %Adam of %Adam% vereisen echter een volledige tabelscan, wat traag kan zijn voor grote tabellen.
2. Vermijd voorloop-wildcards: Een patroon starten met %, zoals %pattern, schakelt indexgebruik uit, omdat de database elk record moet bekijken.
3. Collation en case-insensitieve matching: Functies zoals LOWER() of UPPER() op kolommen gebruiken voor case-insensitieve zoekacties kan ook voorkomen dat indexen worden gebruikt. Stel in plaats daarvan de database-collation passend in voor case-insensitieve vergelijkingen.
4. Alternatieve benaderingen: Overweeg voor grote datasets full-text search of databasespecifieke zoekfeatures, zoals GIN-indexen in PostgreSQL of FULLTEXT-indexen in MySQL, bij complexe of frequente tekenreeks-matching.
5. Selectieve queries: Beperk de scope van je queries met extra filters, zoals datumbereiken of numerieke kolommen, om de data te verkleinen die door de operator LIKE wordt verwerkt.

LIKE versus full-text search

Full-text search is beschikbaar in databases zoals MySQL, PostgreSQL en SQL Server. Hoewel LIKE goed werkt voor eenvoudige patroonherkenning, is full-text search gebouwd voor geavanceerdere tekstqueries. Zo verhouden de twee benaderingen zich:

Voor meer geavanceerde tekenreeksbewerkingen buiten patroonherkenning, zie de tutorial SQL CONTAINS.

Gebruik SQL LIKE met vertrouwen

SQL-functies beheersen is cruciaal om te slagen in data science, en SQL’s LIKE-commando is daarop geen uitzondering. Een goede beheersing van SQL versnelt je analytische vooruitgang, dus zorg dat je het goed leert!

Voor meer bronnen over SQL, bekijk ook:

• Introduction to SQL-cursus
• Intermediate SQL-cursus
• Introductie tot de WHERE-clausule in SQL
• SQL Subquery: een uitgebreide gids
• Introductie tot SQL Joins
• Learn SQL-hub
• SQL Basics Cheat Sheet