SQL CASE en functies: berekende kolommen

Een resultaatkolom kan een waarde tonen die nergens in de tabel is opgeslagen. SQL berekent die op basis van de huidige rij. Een expressie combineert kolommen, vaste waarden, operatoren en functieaanroepen.

Bereken voor elke rij een waarde

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders;

Bij hoeveelheid 3 en eenheidsprijs 12,50 toont de nieuwe kolom 37.5. Elke rij levert zijn eigen hoeveelheid en prijs. De query laat de opgeslagen kolommen ongemoeid. Een scalaire functie, zoals ROUND(unit_price, 0), geeft ook één waarde per invoerrij terug. Als je die aan de SELECT-lijst toevoegt, worden rijen niet gecombineerd. Een aggregatiefunctie zoals SUM of AVG berekent daarentegen een waarde voor een groep.

De naam na AS is een kolomalias: een kolomkop voor het resultaat. Zonder alias gebruikt SQLite de expressie als kolomkop; PostgreSQL gebruikt ?column? voor deze rekenkundige expressie. AS is optioneel, maar maakt een naam gemakkelijker te onderscheiden van een ontbrekende komma.

Kies namen en aanhalingstekens

Gebruik een eenvoudige naam zoals line_total: letters, cijfers en lage streepjes, beginnend met een letter. Een naam met een spatie of een gereserveerd woord heeft dubbele aanhalingstekens nodig: AS "Line Total" of AS "order". Enkele aanhalingstekens markeren tekstwaarden, zoals 'Boston'. MySQL accepteert voor namen ook backticks en SQL Server accepteert rechte haken.

PostgreSQL zet namen zonder aanhalingstekens om naar kleine letters: AS LineTotal levert linetotal op. Dubbele aanhalingstekens behouden de schrijfwijze in AS "LineTotal". SQLite behoudt een alias zoals je die hebt getypt. Namen in kleine letters met lage streepjes werken in beide systemen goed.

Gebruik een alias op het juiste moment

De logische volgorde is FROM, WHERE, SELECT en dan ORDER BY. De tabel wordt gekozen en rijen worden gefilterd voordat uitvoerexpressies hun aliassen krijgen. Het sorteren gebeurt daarna.

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders
WHERE quantity * unit_price > 100
ORDER BY line_total DESC;

ORDER BY kan line_total gebruiken. Standaard-SQL, PostgreSQL en MySQL kunnen die nieuwe alias niet in WHERE gebruiken; herhaal daar de expressie. SQLite accepteert een alias in WHERE als uitbreiding, maar die vorm mislukt als je hem naar PostgreSQL verplaatst.

Een nieuwe alias is ook niet beschikbaar voor een andere expressie in dezelfde SELECT-lijst. Na unit_price * 1.08 AS with_tax mislukt with_tax - unit_price AS tax als de tabel geen kolom with_tax heeft. Schrijf in plaats daarvan unit_price * 1.08 - unit_price AS tax.

Onderzoek een verrassend resultaat

SELECT first_name last_name FROM customers; is geldige SQL. Het toont één kolom met voornamen met de alias last_name. Om beide kolommen op te halen, schrijf je SELECT first_name, last_name FROM customers;. Een ontbrekende komma kan dus zonder foutmelding een misleidend resultaat opleveren.

Voor een snelle controle van een expressie is geen tabel nodig: SELECT 19.99 * 3 AS total; geeft 59.97 terug in SQLite en PostgreSQL. Oracle vóór Release 23 vereist FROM DUAL voor deze query; Release 23 en later staan ook SELECT zonder FROM toe.

Rekenkundige expressies zetten oorspronkelijke hoeveelheden om in bedragen, percentages en verschillen. De operatoren zijn +, -, *, / en %; modulo geeft de rest na deling.

Bepaal de rekenvolgorde

Vermenigvuldigen, delen en modulo gaan vóór optellen en aftrekken. Operatoren met dezelfde prioriteit worden van links naar rechts uitgevoerd. Zo geeft 10 + 3 * 2 de uitkomst 16, terwijl (10 + 3) * 2 de uitkomst 26 geeft.

Voor hoeveelheid 3, eenheidsprijs 12,50 en kortingspercentage 10:

quantity * unit_price * (1 - discount_pct / 100.0)

Eerst geeft 10 / 100.0 de uitkomst 0.1. De haakjes geven het betaalde deel, 0.9. Het brutobedrag is 37.5, dus het nettobedrag is 33.75. Zonder haakjes trekt quantity * unit_price * 1 - discount_pct / 100.0 de waarde 0.1 af van 37.5 en geeft 37.4.

Behoud breuken bij het delen

In SQLite, PostgreSQL en SQL Server wordt bij deling van een geheel getal door een geheel getal het deel achter de komma afgekapt in de richting van nul: 7 / 2 geeft 3 en -7 / 2 geeft -3. MySQL en Oracle geven bij / een decimaal antwoord. Maak in SQLite en PostgreSQL vóór de deling een operand decimaal: 7 / 2.0, 7 * 1.0 / 2 of CAST(7 AS REAL) / 2 geeft 3.5. Bij CAST(7 / 2 AS REAL) is het te laat en krijg je 3.0.

Bij een stijging van 80 naar 92 geeft (92 - 80) * 100.0 / 80 de uitkomst 15.0 procent. Als je het gehele verschil eerst door 80 deelt, gaat het deel achter de komma verloren. Modulo geeft de rest: 135 / 60 is 2 uur en 135 % 60 is 15 minuten. In SQLite en PostgreSQL geeft -7 % 3 de uitkomst -1; de rest volgt het teken van de linkeroperand. Oracle schrijft MOD(17, 5) in plaats van %.

Een deler nul geeft een fout in PostgreSQL en SQL Server; SQLite en MySQL geven NULL terug. Met amount / NULLIF(divisor, 0) geven deze systemen bij nul allemaal NULL terug.

Rond af of kies een geheel getal

ROUND(x, n) rondt af op n decimalen. Een lamp met een verkoopprijs van 24,00 en kosten van 15,50 heeft een winst van 8.5. ROUND((price - cost) / price * 100, 1) geeft een marge van 35.4 procent. PostgreSQL ondersteunt negatieve aantallen decimalen: ROUND(1234.5678, -2) geeft 1200; SQLite behandelt het aantal decimalen als nul en geeft 1235.0.

PostgreSQL biedt deze functies. SQLite biedt ABS; voor CEIL, FLOOR en TRUNC is een versie nodig die met wiskundige functies is gecompileerd, beschikbaar vanaf 3.35.

Stem de precisie af op de gegevens

ROUND in SQLite en ROUND voor numeric in PostgreSQL ronden waarden precies halverwege twee uitkomsten af van nul af: ROUND(-2.5) geeft -3. PostgreSQL rondt drijvendekommagetallen bij zulke waarden meestal af naar het dichtstbijzijnde even gehele getal; ROUND met twee argumenten vereist numeric, dus gebruik ROUND(CAST(x AS numeric), 2) voor een kolom met dubbele precisie.

Drijvendekommagetallen benaderen veel decimale breuken. In SQLite kan 0.1 + 0.2 = 0.3 onwaar zijn en kan ROUND(2.675, 2) de uitkomst 2.67 geven. Decimale literals in PostgreSQL gebruiken het exacte type numeric. Gebruik voor exacte geldbedragen NUMERIC/DECIMAL in PostgreSQL of gehele centen in SQLite. Een kolom als DECIMAL declareren in SQLite zorgt niet voor exacte decimale opslag. Behoud de precisie tijdens berekeningen en rond af voor de weergave.

Het gegevenstype van een waarde beïnvloedt berekeningen, vergelijkingen en sortering. Het getal 42 verschilt van de tekst '42', ook als ze in een resultatenoverzicht hetzelfde worden weergegeven.

Converteer een waarde expliciet

Veelvoorkomende typen zijn INTEGER voor gehele getallen, NUMERIC of DECIMAL voor exacte decimalen in PostgreSQL, REAL of double precision voor benaderde getallen, TEXT of VARCHAR voor tekst en DATE, TIME, TIMESTAMP en BOOLEAN waar die worden ondersteund.

SELECT CAST('42' AS INTEGER) AS number_value,
       CAST(42 AS TEXT) AS text_value;

CAST(expression AS type) is standaard-SQL. Het eerste resultaat is een getal; het tweede is tekst. PostgreSQL accepteert ook '42'::integer. Conversie van NULL geeft nog steeds NULL.

Schrijf voor decimale deling in SQLite CAST(quantity AS REAL) / 4; schrijf in PostgreSQL CAST(quantity AS NUMERIC) / 4. Hoeveelheid 7 geeft 1.75. CAST(7 AS NUMERIC) / 2 in SQLite geeft nog steeds 3: NUMERIC laat die gehele waarde als geheel getal opgeslagen. REAL is hiervoor de geschikte conversie in SQLite.

Weet wat conversie naar een geheel getal doet

SQLite kapt het deel achter de komma af in de richting van nul. PostgreSQL rondt een numerieke waarde af op het dichtstbijzijnde gehele getal. Gebruik eerst ROUND, TRUNC, FLOOR of CEIL als de gewenste regel ertoe doet. PostgreSQL wijst tekst af die geen geldig getal is. SQLite leest een numeriek begin: '12abc' wordt 12 en '12.5' wordt 12 bij conversie naar INTEGER. Nul na een conversie in SQLite bewijst dus niet dat de oorspronkelijke tekst een geldige nul was. TRY_CAST in SQL Server geeft NULL terug bij een mislukte conversie.

Onderscheid hoeveelheden van identificatoren

Tekst wordt teken voor teken gesorteerd. De tekenreeksen '9', '10' en '100' worden gesorteerd als '10', '100', '9'. Gebruik ORDER BY CAST(code AS INTEGER) om echte numerieke hoeveelheden die als tekst zijn opgeslagen te ordenen, nadat je hebt gecontroleerd dat het geldige getallen zijn.

Een Amerikaanse postcode zoals '02134' is een identificator. Conversie geeft 2134 en laat de eerste nul weg. Ook telefoonnummers horen als tekst te worden opgeslagen: hun cijfers duiden iets aan en beschrijven geen hoeveelheid die je kunt optellen of middelen.

Herken automatische conversies

Zowel SQLite als PostgreSQL zet het getal in 'Order #' || 42 om naar tekst, met als resultaat 'Order #42'. Beide berekenen ook de literal tussen aanhalingstekens in '5' + 3 als een getal. In PostgreSQL ligt het type van die literal aanvankelijk nog open; een TEXT-kolom heeft niet dezelfde vrijheid. Als je 3 bij een TEXT-kolom optelt, mislukt dat, dus converteer de kolom expliciet.

SQLite geeft kolommen een typevoorkeur, typeaffiniteit genoemd, en kan verschillende waardetypen in één gewone kolom bewaren. typeof(42), typeof(4.2), typeof('42') en typeof(NULL) geven 'integer', 'real', 'text' en 'null'. SQLite heeft geen afzonderlijk DATE-type: CAST('2026-03-14' AS DATE) geeft 2026. Gebruik date('2026-03-14') voor de datumfuncties.

Oracle biedt TO_NUMBER, TO_CHAR en TO_DATE. TO_CHAR en TO_DATE in PostgreSQL gebruiken opmaakpatronen zoals 'MM/DD/YYYY'. CONVERT in SQL Server gebruikt een numerieke stijlcode: CONVERT(date, '03/14/2026', 101) leest de Amerikaanse datumnotatie.

NULL geeft een ontbrekende of onbekende waarde aan. Een lege cel in het resultaat kan NULL weergeven, maar NULL is geen gewone waarde die je met = vergelijkt.

Volg een ontbrekende invoer door een expressie

Nul is een bekend getal. Lege tekst, '', is een bekende tekenreeks zonder tekens. De tekst 'N/A' is een geregistreerd label. NULL zegt dat er geen waarde beschikbaar is. Oracle behandelt lege tekst als NULL; SQLite en PostgreSQL maken er onderscheid tussen.

De meeste rekenkundige bewerkingen en functies geven een ontbrekende invoer door als NULL. Bij salaris 50.000 en bonus NULL geeft salary + bonus de uitkomst NULL. Het totaal is onbekend omdat de bonus onbekend is. Ook UPPER(NULL), ROUND(NULL, 2) en 'Ana ' || NULL geven NULL terug in SQLite en PostgreSQL.

Test rechtstreeks op NULL

bonus = NULL, bonus <> NULL en zelfs NULL = NULL leveren UNKNOWN op. Van twee ontbrekende waarden is niet bekend dat ze gelijk zijn. Gebruik bonus IS NULL of bonus IS NOT NULL. Deze tests geven altijd TRUE of FALSE terug.

SQL-voorwaarden hebben drie mogelijke resultaten: TRUE, FALSE en UNKNOWN. SQLite toont TRUE als 1, FALSE als 0 en UNKNOWN als NULL.

Begrijp welke rijen WHERE behoudt

WHERE behoudt alleen TRUE. Zowel FALSE als UNKNOWN laat de rij wegvallen. Bij bonussen van 1000, 0, NULL, 2500 en NULL behoudt WHERE bonus < 1000 alleen de nul. WHERE bonus >= 1000 behoudt 1000 en 2500. Deze filters omvatten drie rijen, geen vijf. Ook WHERE NOT (bonus < 1000) behoudt alleen de twee bekende bonussen van minstens 1000.

Hetzelfde probleem geldt voor <>. Bij de steden Boston, Denver, NULL, Boston en Austin behoudt WHERE city <> 'Boston' Denver en Austin. Neem de onbekende stad expliciet mee:

WHERE city <> 'Boston' OR city IS NULL

Dit behoudt drie rijen. NOT haalt ontbrekende waarden niet automatisch terug.

Let op bij lijsten en vergelijkingen

3 IN (1, 2, NULL) is UNKNOWN: het ontbrekende lid kan 3 zijn. Voor x NOT IN (1, 2, NULL) voldoet een overeenkomende waarde niet aan NOT IN en geeft elke niet-overeenkomende waarde UNKNOWN. In een WHERE-clausule behoudt deze vaste lijst geen rijen. Verwijder een onbedoelde NULL uit een uitsluitingslijst voordat je die gebruikt.

Gebruik a IS NOT DISTINCT FROM b voor een gelijkheidsvergelijking die twee NULL-waarden als gelijk behandelt. Het tegenovergestelde, a IS DISTINCT FROM b, is TRUE als de ene waarde NULL is en de andere niet; twee NULL-waarden zijn niet verschillend. PostgreSQL en SQLite vanaf 3.39 ondersteunen deze vormen. SQLite heeft ook IS en IS NOT; MySQL schrijft NULL-veilige gelijkheid als <=>.

De standaardplaats voor NULL bij het sorteren verschilt: SQLite zet NULL vooraan bij oplopend sorteren; PostgreSQL zet NULL achteraan. Geef de plaats aan als die ertoe doet: ORDER BY bonus DESC NULLS LAST zet bekende bonussen in aflopende volgorde en ontbrekende bonussen aan het einde.

Soms hebben ontbrekende waarden een vervangingswaarde nodig en soms moeten ze ontbreken. Die keuze maakt deel uit van de betekenis van een rapport. COALESCE vult een ontbrekende waarde aan; NULLIF maakt van een overeenkomende plaatsaanduiding een ontbrekende waarde.

Kies de eerste beschikbare waarde

COALESCE(a, b, ...) controleert van links naar rechts en geeft het eerste argument terug dat niet NULL is. Het geeft NULL terug als elk argument NULL is.

SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;

Zonder mobiel nummer en met huistelefoonnummer '555-0100' is het contactnummer '555-0100'. Als geen van beide nummers is geregistreerd, is het 'no phone'. Een bekende nul of lege tekenreeks geldt als beschikbaar: COALESCE slaat NULL over, geen waarden die leeg of onwaar lijken.

Voor de beloning geeft salary + COALESCE(bonus, 0) de uitkomst 50.000 als het salaris 50.000 is en de bonus NULL. Dit is passend als een ontbrekende bonus betekent dat er geen is betaald. Een ontbrekende productbeoordeling betekent geen beoordeling van nul; door die te vervangen zou je een slechte beoordeling verzinnen. Laat dat getal ontbreken of maak een tekstlabel voor de weergave.

Houd vervangingstypen compatibel

PostgreSQL kan de gehele waarde rating en de tekst 'not rated' niet combineren in één numeriek resultaat. Converteer de bekende beoordelingen eerst naar tekst:

COALESCE(CAST(rating AS TEXT), 'not rated')

Een beoordeling van 4 wordt weergegeven als de tekst '4'; een NULL wordt weergegeven als 'not rated'. SQLite accepteert gemengde numerieke en tekstargumenten, maar de uitvoer kan dan verschillende typen bevatten. Maak het gewenste resultaattype duidelijk als je een query tussen databases verplaatst.

COALESCE is standaard-SQL en accepteert twee of meer argumenten. De gebruikelijke alternatieven met twee argumenten zijn IFNULL in SQLite en MySQL, ISNULL in SQL Server en NVL in Oracle.

Zet een plaatsaanduiding om in NULL

NULLIF(a, b) geeft NULL terug als de argumenten gelijk zijn; anders geeft het het eerste argument terug. NULLIF(0, 0) is NULL, terwijl NULLIF(5, 0) gelijk is aan 5.

Een bruikbare expressie voor een percentage is:

orders * 100.0 / NULLIF(visits, 0) AS conversion_pct

Bij 12 orders en 300 bezoeken blijft de deler 300 en is het percentage 4.0. Bij nul bezoeken wordt de deler NULL en wordt het percentage NULL. PostgreSQL voorkomt zo een fout door deling door nul en het resultaat geeft correct aan dat het percentage niet is gedefinieerd. Dat percentage vervangen door nul zou iets anders beweren.

Schoon ontbrekende waarden op voordat je een label geeft

COALESCE(NULLIF(phone, ''), 'unknown')

De binnenste NULLIF zet een lege tekenreeks om in NULL. De buitenste COALESCE geeft vervolgens zowel een oorspronkelijke NULL als een lege tekenreeks het label 'unknown'. Een geregistreerd telefoonnummer komt er ongewijzigd doorheen.

Neem bij een optionele tweede voornaam de afsluitende spatie op binnen de vervangingswaarde:

first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name

Een tweede voornaam die NULL is, voegt niets toe en geeft 'Ana Lopez'. Een tweede voornaam 'Maria' voegt 'Maria ' toe en geeft 'Ana Maria Lopez'.

Deze expressies veranderen alleen het queryresultaat. Ze laten de oorspronkelijke waarden in de tabel staan. Een filter zoals WHERE COALESCE(bonus, 0) = 0 groepeert bekende nulbonussen en ontbrekende bonussen samen, dus gebruik dit als je die twee groepen wilt hebben.

SQL behandelt een tekstwaarde als een tekenreeks. Tekstfuncties kunnen een volledige naam samenstellen of een e-mailadres opschonen voor vergelijking. Hun resultaten verschijnen in de query; opgeslagen tekst blijft ongewijzigd.

Schrijf tekst en voeg die samen

Tekstliterals gebruiken enkele aanhalingstekens. Om een apostrof op te nemen, verdubbel je die: 'O''Brien' stelt “O'Brien” voor. Een paar enkele aanhalingstekens, '', is lege tekst.

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

De concatenatie-operator || voegt de onderdelen precies zoals geschreven samen. Met 'Ana' en 'Lopez' is het resultaat 'Ana Lopez'. De middelste literal levert de spatie; concatenatie voegt die niet automatisch in.

SQLite, PostgreSQL en Oracle gebruiken ||. In MySQL betekent || standaard OR, dus gebruik daar CONCAT. SQL Server gebruikt + en ondersteunt vanaf 2025 ook ||. Om een numerieke orderidentificatie samen te voegen met tekst, zetten SQLite en PostgreSQL die in 'Order #' || order_id om naar tekst. Converteer het getal eerst bij + in SQL Server: 'Order #' + CAST(order_id AS VARCHAR(10)).

Kies hoe je een ontbrekend onderdeel samenvoegt

In SQLite en PostgreSQL maakt een NULL-operand het resultaat van || NULL. Oracle behandelt dat ontbrekende onderdeel als lege tekst. Ook CONCAT verschilt per database:

CONCAT in Oracle gebruikt precies twee argumenten. CONCAT_WS(' ', first_name, middle_name, last_name) plaatst spaties tussen onderdelen die niet NULL zijn, dus een ontbrekende tweede voornaam geeft 'Ana Lopez'. SQLite heeft CONCAT en CONCAT_WS toegevoegd in 3.44. NULL overslaan verwijdert nog steeds geen lege tekenreeks.

Zet letters om en verwijder spaties aan de randen

UPPER('boston') geeft 'BOSTON'; LOWER zet letters om naar kleine letters. INITCAP('ana LOPEZ') in PostgreSQL geeft 'Ana Lopez'. SQLite heeft geen INITCAP en de ingebouwde omzetting verandert alleen A–Z: UPPER('é') blijft 'é' zonder de ICU-uitbreiding. De omzetting in PostgreSQL volgt de taal- en regio-instellingen.

TRIM verwijdert spaties aan beide uiteinden; LTRIM verwijdert ze alleen aan het begin en RTRIM alleen aan het einde. TRIM(' New York ') geeft 'New York' en behoudt de twee spaties binnenin. Om x aan beide uiteinden te verwijderen, schrijft SQLite TRIM('xxBostonxx', 'x'); PostgreSQL schrijft TRIM(BOTH 'x' FROM 'xxBostonxx').

Vervang tekens en nest opschoonstappen

REPLACE('555-0100', '-', '') geeft '5550100'. Het vervangt elke exacte overeenkomst. Hoofdletters en kleine letters doen ertoe: 'st' vervangen in 'Main St' verandert niets, omdat 'St' een hoofdletter S heeft.

SELECT LOWER(TRIM(email)) AS email_clean
FROM customers;

Voor ' Ana@Example.COM ' geeft TRIM eerst 'Ana@Example.COM', waarna LOWER 'ana@example.com' geeft. Nesten betekent dat de binnenste functie de invoer voor de volgende levert.

Gewone tekstgelijkheid is standaard hoofdlettergevoelig in SQLite en PostgreSQL. WHERE LOWER(TRIM(email)) = 'ana@example.com' kan invoer met andere hoofdletters en spaties aan de randen vinden. De standaard sorteerregels van MySQL negeren hoofdletters en LIKE in SQLite negeert het verschil tussen hoofdletters en kleine letters voor A–Z, hoewel de standaardvergelijking met = dat niet doet.

Tekstfuncties zoeken en selecteren delen van tekst op basis van tekenposities. In SQL beginnen posities bij 1, dus het eerste teken staat op positie 1. Een gevonden positie en een aantal tekens zijn verschillende invoerwaarden.

Bepaal de lengte en haal een deel van een tekenreeks op

LENGTH('Boston') geeft 6. Spaties tellen mee: LENGTH(' Bo ') geeft 4. Lege tekst heeft lengte 0; LENGTH(NULL) is NULL. LENGTH in MySQL telt bytes, dus gebruik daar CHAR_LENGTH voor tekens. SQL Server gebruikt LEN, dat spaties aan het einde negeert.

SELECT SUBSTR('Boston', 1, 3) AS beginning,
       SUBSTR('Boston', 4) AS ending;

De resultaten zijn 'Bos' en 'ton'. De eerste aanroep neemt drie tekens vanaf positie 1. De tweede laat het aantal weg en neemt alles vanaf positie 4. PostgreSQL schrijft ook SUBSTRING('Boston' FROM 4 FOR 3). SQLite accepteert de naam SUBSTRING vanaf 3.34. SQL Server gebruikt SUBSTRING(text, start, count); het aantal is verplicht tot en met 2022 en optioneel vanaf 2025.

SQLite kan vanaf rechts tellen: SUBSTR('Boston', -3) geeft 'ton'. Negatieve startposities in PostgreSQL tellen niet terug vanaf het einde. Gebruik daar RIGHT('Boston', 3); LEFT haalt tekens vanaf het begin op. SQLite heeft geen LEFT en geen RIGHT.

Zoek het splitsingspunt

INSTR(text, search) in SQLite geeft de eerste positie terug waarop een overeenkomst staat. PostgreSQL gebruikt STRPOS of POSITION(search IN text). Deze functies geven 0 terug als er geen overeenkomst is. De zoekopdracht is hoofdlettergevoelig: INSTR('Ana', 'a') is 3.

In 'ana@example.com' staat de @ op positie 4. De gebruikersnaam eindigt er vlak voor; het domein begint er vlak na:

SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_name,
SUBSTR(email, INSTR(email, '@') + 1) AS domain

De eerste expressie wordt SUBSTR(email, 1, 3) en levert 'ana' op. De tweede begint op 5 en levert 'example.com' op. Als je - 1 weglaat, komt @ in de gebruikersnaam terecht. Als je + 1 weglaat, komt het teken aan het begin van het domein te staan. Vervang in PostgreSQL INSTR door STRPOS of gebruik SPLIT_PART(email, '@', 2) voor het domein.

Ga om met een ontbrekend scheidingsteken

Voor 'anaexample.com' geeft INSTR 0 terug. De domeinformule begint op 1 en geeft ten onrechte de hele tekenreeks terug. De formule voor de gebruikersnaam geeft in SQLite lege tekst; PostgreSQL wijst de negatieve lengte van de deeltekenreeks af. Beveilig het ophalen:

CASE
  WHEN INSTR(email, '@') > 0
  THEN SUBSTR(email, INSTR(email, '@') + 1)
END

Dit geeft NULL terug als het scheidingsteken ontbreekt. De controle zoekt naar een scheidingsteken en controleert niet alle regels voor een geldig e-mailadres.

Maak korte labels en codes met een vaste breedte

SUBSTR(first_name, 1, 1) || '. ' || last_name geeft 'A. Lopez' voor Ana Lopez. WHERE LENGTH(zip_code) <> 5 vindt geregistreerde Amerikaanse postcodes als tekenreeks met de verkeerde lengte; NULL-lengtes leveren UNKNOWN op en vallen weg.

LPAD('42', 5, '0') in PostgreSQL geeft '00042'; RPAD vult rechts aan. SQLite kan die code maken met SUBSTR('00000' || 42, -5). Door slechts vijf tekens te behouden, wordt een langere waarde ook afgekapt, dus deze expressie met vaste breedte vereist een code die past.

Een datum is gemakkelijker om mee te rekenen als de opslag en schrijfwijze duidelijk zijn. De tekst '03/04/2026' kan 4 maart of 3 april betekenen. Een ISO-datum, '2026-03-14', zet jaar, maand en dag in een ondubbelzinnige volgorde.

Stem de waarde af op het type

PostgreSQL heeft DATE voor een kalenderdag, TIME voor een tijdstip en TIMESTAMP voor een datum en tijdstip. TIMESTAMP WITH TIME ZONE, ook timestamptz genoemd, slaat een moment op in UTC en toont het in de tijdzone van de sessie. Een gewone TIMESTAMP heeft die tijdzonebetekenis niet.

SQLite slaat datums en tijden op als ISO-tekst of als getallen, niet als afzonderlijke datum- en tijdtypen. Numerieke vormen zijn onder meer Juliaanse dagnummers en Unixtijd. De datumfuncties interpreteren deze waarden en geven een datum, tijdstip of numerieke maat terug.

Gebruik ISO-tekst met een vaste breedte zoals '2026-03-14', inclusief voorloopnullen. Voeg een tijdstip toe volgens de 24-uursklok: '2026-03-14 09:30:00'. De getypeerde literal DATE '2026-03-14' van PostgreSQL maakt expliciet een datum.

Behoud de chronologische volgorde

ISO-tekst met vaste breedte wordt in datumvolgorde vergeleken, omdat die met het grootste onderdeel begint. '2026-03-14' komt vóór '2026-11-02'. Amerikaanse tekst voor de weergave heeft deze eigenschap niet: als tekenreeksen wordt '11/02/2025' na '03/14/2026' gesorteerd, hoewel die datum eerder is.

De omzettingsfunctie van SQLite heeft de ondersteunde notatie nodig. date('2026-3-14') geeft NULL terug omdat de maand niet uit twee cijfers bestaat. De functie kan ook een onmogelijke datum normaliseren: date('2026-02-30') geeft '2026-03-02' terug. Een resultaat dat niet NULL is, bewijst op zichzelf niet dat de invoer een geldige kalenderdag was. PostgreSQL wijst DATE '2026-02-30' af.

Lees tekst in een andere notatie

PostgreSQL en Oracle gebruiken TO_DATE('03/14/2026', 'MM/DD/YYYY') om een Amerikaanse notatie te lezen. PostgreSQL leest ISO-tekst ook met CAST('2026-03-14' AS DATE) of '2026-03-14'::date.

SQLite heeft geen TO_DATE-omzettingsfunctie. Bouw voor tekst in vaste MM/DD/YYYY-notatie in d de ISO-tekst opnieuw op:

SUBSTR(d, 7, 4) || '-' ||
SUBSTR(d, 1, 2) || '-' || SUBSTR(d, 4, 2)

De onderdelen zijn het jaar '2026', de maand '03' en de dag '14'. Het resultaat is '2026-03-14', dat de datumfuncties van SQLite kunnen lezen. De posities werken alleen als de invoer daadwerkelijk de vaste notatie gebruikt.

Haal de huidige datum en tijd op

CURRENT_DATE en CURRENT_TIMESTAMP werken in SQLite en PostgreSQL. SQLite geeft UTC-tekst terug; date('now', 'localtime') geeft de lokale datum. Rond middernacht kunnen UTC en de lokale kalenderdatum verschillen.

PostgreSQL gebruikt de tijdzone van de sessie. CURRENT_TIMESTAMP en NOW() geven de begintijd van de huidige transactie weer, dus wachten binnen die transactie verandert hun waarde niet. MySQL biedt NOW() en CURDATE(); SQL Server heeft GETDATE() en SYSDATETIME(); Oracle heeft SYSDATE, inclusief een tijdstip, en CURRENT_DATE.

Gebruik om een berekening van een vervaldatum te controleren eerst een vast uitgangspunt zoals DATE '2026-10-21' in PostgreSQL of '2026-10-21' in SQLite. Je kunt het resultaat dan met een kalender vergelijken zonder dat het antwoord de volgende dag verandert.

Haal een datumonderdeel op als je een jaar of weekdag nodig hebt om mee te rekenen. Maak een datum op als je tekst voor een lezer nodig hebt. Deze bewerkingen kunnen verschillende typen opleveren, ook als de weergegeven tekens er hetzelfde uitzien.

Haal een jaar, maand of weekdag op

PostgreSQL gebruikt het standaardcommando EXTRACT(part FROM value). Voor DATE '2026-03-14' geeft YEAR de uitkomst 2026 en MONTH de uitkomst 3, beide als getal. MySQL en Oracle ondersteunen EXTRACT ook.

SQLite gebruikt strftime(format, value), dat tekst teruggeeft:

%w in SQLite en DOW in PostgreSQL nummeren de dagen van zondag als 0 tot en met zaterdag als 6. 14 maart 2026 is een zaterdag. ISODOW in PostgreSQL nummert daarentegen van maandag als 1 tot en met zondag als 7. Een weekendfilter in SQLite is WHERE strftime('%w', order_date) IN ('0', '6').

Vergelijk de juiste typen

strftime('%m', order_date) = 3 is onwaar voor een datum in maart in SQLite: de functie geeft de tekst '03' terug, niet het getal 3. Vergelijk met = '03' of converteer eerst:

CAST(strftime('%m', order_date) AS INTEGER) = 3

Dit is ook nuttig in de kwartaalexpressie (CAST(strftime('%m', d) AS INTEGER) + 2) / 3. Gehele deling geeft kwartaal 1 voor maart en kwartaal 4 voor november. PostgreSQL heeft rechtstreeks EXTRACT(QUARTER FROM d).

Duid een maand aan als volledige periode

strftime('%Y-%m', order_date) maakt in SQLite een label zoals '2026-03'. date(order_date, 'start of month') maakt '2026-03-01'. PostgreSQL kan TO_CHAR(order_date, 'YYYY-MM') gebruiken voor een label of DATE_TRUNC('month', order_date) voor het eerste moment van die maand.

Behoud het jaar in een periodelabel. Een losse maandwaarde van 3 zou maart uit verschillende jaren combineren. De waarde voor de eerste van de maand kan de waarde voor berekeningen en sortering blijven, terwijl een afzonderlijke expressie voor de weergave zorgt.

Maak op bij de uitvoer

In PostgreSQL geeft TO_CHAR(DATE '2026-03-14', 'MM/DD/YYYY') de tekst '03/14/2026' en geeft het patroon 'Mon DD, YYYY' de tekst 'Mar 14, 2026'. Oracle gebruikt deze patronen ook. De Amerikaanse notatie in SQLite is strftime('%m/%d/%Y', '2026-03-14').

Patroonletters horen bij hun database. In PostgreSQL betekent MM maand en MI minuut. In SQLite betekent %m maand en %M minuut; als je die hoofdletter verandert, verandert de gevraagde informatie.

SELECT TO_CHAR(order_date, 'MM/DD/YYYY') AS order_day
FROM orders
ORDER BY order_date;

Hier toont PostgreSQL leesbare tekst, maar sorteert het op de werkelijke datums. Sorteren op order_day zou de weergegeven tekenreeksen eerst op maand sorteren en datums uit verschillende jaren in de verkeerde volgorde kunnen zetten.

Bij kalenderberekeningen moet je rekening houden met maandlengtes, tijdsonderdelen en het soort antwoord dat nodig is. Een aantal dagen, een kalendermaand en een voltooid levensjaar zijn verschillende maten.

Tel dagen en kalendermaanden op

PostgreSQL schrijft DATE '2026-03-14' + 30; SQLite schrijft date('2026-03-14', '+30 days'). Beide komen uit op 13 april: na 17 dagen is maart voorbij en de overige 13 dagen brengen je op 13 april. De rechtstreekse expressie '2026-03-14' + 30 van SQLite geeft daarentegen 2056, omdat de rekenkundige bewerking het jaartal aan het begin als getal leest.

Voor het einde van de maand is een expliciete regel nodig. DATE '2026-01-31' + INTERVAL '1 month' in PostgreSQL geeft 28 februari om middernacht, met de laatste beschikbare dag. date('2026-01-31', '+1 month') in SQLite telt overtollige dagen door in maart en geeft '2026-03-03'. Voeg vanaf SQLite 3.46 na de verschuiving 'floor' toe om '2026-02-28' te kiezen. ADD_MONTHS in Oracle verschuift ook naar de laatste dag van de kortere maand.

Bepaal de verstreken tijd

Aftrekken van DATE-waarden in PostgreSQL geeft hele dagen terug: DATE '2026-04-13' - DATE '2026-03-14' is 30. SQLite trekt numerieke dagwaarden af: julianday('2026-04-13') - julianday('2026-03-14') is 30.0. SQL Server gebruikt DATEDIFF(day, start, end); MySQL gebruikt DATEDIFF(end, start).

Voor tijdstempels van 14 maart om 09:30 tot 18:00 geeft aftrekken in PostgreSQL een interval van 08:30:00; EXTRACT(EPOCH FROM interval) / 3600 drukt dit uit als 8,5 uur. Vanaf SQLite 3.38 kun je schrijven:

(unixepoch('2026-03-14 18:00') -
 unixepoch('2026-03-14 09:30')) / 3600.0

Dit geeft 8.5. Een verschil tussen Juliaanse dagnummers vermenigvuldigd met 24 kan 8.49999999627471 geven door benadering met drijvendekommagetallen; rond de weergave af. Een tijdsduur tussen tijdstempels die een deel achter de komma bevat, moet je niet naar INTEGER converteren voordat de gewenste eenheid is berekend.

Tel voltooide verjaardagen

Iemand die op 2 november 1990 is geboren, is op 21 oktober 2026 35 jaar. De jaartallen verschillen 36, maar de verjaardag is nog niet geweest. PostgreSQL gebruikt EXTRACT(YEAR FROM AGE(DATE '2026-10-21', DATE '1990-11-02')), met als resultaat 35.

SQLite trekt er één af als de verjaardag dit jaar nog moet komen:

strftime('%Y', '2026-10-21') - strftime('%Y', birth_date)
- (strftime('%m-%d', '2026-10-21') < strftime('%m-%d', birth_date))

De vergelijking levert 1 op als de verjaardag nog moet komen en anders 0. Verstreken dagen delen door 365 is onbetrouwbaar: op 1 november 2026 heeft deze persoon 13.148 dagen geleefd, wat gedeeld door 365 de uitkomst 36,02 geeft, hoewel de 36e verjaardag morgen is. DATEDIFF in SQL Server telt grenzen: van 31 december naar 1 januari gaan telt als een jaargrens, niet als een voltooid levensjaar.

Neem elke tijdstempel in een periode mee

BETWEEN sluit beide grenspunten in. Voor DATE-kolommen omvat 1 maart tot en met 31 maart heel maart. Voor tijdstempels betekent een bovengrens van '2026-03-31' middernacht aan het begin van de laatste dag, waardoor de rest van die dag ontbreekt.

WHERE ordered_at >= '2026-03-01'
  AND ordered_at < '2026-04-01'

Dit halfopen bereik sluit het begin in en het begin van de volgende periode uit. Zo valt elk tijdstip in maart binnen het bereik zonder een laatste fractie van een seconde te kiezen.

Achterstallige datums voldoen aan due_date < CURRENT_DATE. De ondergrens voor de afgelopen 30 dagen is CURRENT_DATE - 30 in PostgreSQL en date('now', '-30 days') in SQLite. De dagen tot een deadline zijn due_date - CURRENT_DATE voor DATE-waarden in PostgreSQL en julianday(due_date) - julianday(date('now')) in SQLite.

Een CASE-expressie kiest voor elke rij één resultaat. Je kunt die gebruiken om een prijsklasse een label te geven, kosten te berekenen of een record te markeren dat aandacht nodig heeft.

Lees de vorm met voorwaarden

CASE
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END AS size_band

Elke WHEN bevat een voorwaarde en THEN bevat de waarde die wordt teruggegeven als die voorwaarde TRUE is. De database controleert van boven naar beneden en neemt de eerste voorwaarde die waar is. END sluit de expressie af; daarna kan een alias volgen. Als geen voorwaarde TRUE is en ELSE ontbreekt, is het resultaat NULL.

Een totaal van 35 voldoet aan de eerste voorwaarde en wordt 'small'. Een totaal van 50 voldoet niet aan de eerste maar wel aan de tweede en wordt 'medium'. Hetzelfde geldt voor 199,99. Een totaal van 200 komt bij ELSE en wordt 'large'.

Stem volgorde en grenzen af op de regel

De tweede tak heeft geen total >= 50 nodig: een waarde onder 50 is al door de eerste tak teruggegeven. Oplopende grenswaarden met < werken omdat eerdere takken de kleinere waarden wegnemen. Aflopende grenswaarden met >= kunnen om dezelfde reden werken.

Als je WHEN total < 200 eerst zet, wordt een latere tak met < 50 onbereikbaar. Een totaal van 35 voldoet al aan < 200 en stopt daar dus. CASE kiest niet de meest specifieke voorwaarde en controleert niet welk label het beste klinkt.

Vergelijk de bedrijfsregel met elk grenspunt. < 50 plaatst precies 50 in de middelste klasse; <= 50 plaatst die waarde in de kleine klasse. Controleer 49,99, 50, 199,99 en 200 om beide kanten en de grenzen te bekijken. Als voorwaarden als expliciete intervallen zijn geschreven, laat de grenspunten dan aansluiten zonder tussenruimte of onbedoelde overlap.

Verwerk NULL vóór gewone bereiken

Een NULL-totaal maakt beide vergelijkingen UNKNOWN. In de eerste expressie komt het daarom bij ELSE terecht en wordt het 'large', wat misleidend is. Vang een ontbrekende waarde eerst op:

CASE
  WHEN total IS NULL THEN 'unknown'
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END

ELSE betekent “geen voorwaarde was waar” en kan dus zowel ontbrekende invoer als bekende waarden buiten de geteste bereiken opvangen.

Geef een consistent soort waarde terug

PostgreSQL heeft compatibele THEN- en ELSE-typen nodig. CASE WHEN quantity > 0 THEN unit_price ELSE 'n/a' END mislukt als unit_price numeriek is, omdat 'n/a' niet in een getal kan worden omgezet. Gebruik NULL voor een niet-beschikbaar numeriek resultaat, of converteer de prijs naar tekst als de hele uitvoer een weergavelabel is. SQLite staat gemengde resultaattypen toe, wat latere berekeningen kan bemoeilijken.

Een voorwaarde kan kolommen combineren:

CASE
  WHEN country = 'US' AND subtotal >= 100 THEN 0
  ELSE 7.99
END AS shipping_fee

De teruggegeven kosten kunnen voorkomen in subtotal + CASE ... END AS amount_due. Een CASE is een waarde-expressie, geen opdracht die meerdere kolommen wijzigt; elke resultaatkolom heeft een eigen expressie nodig.

CASE kan ook een deling per rij afschermen: CASE WHEN visits = 0 THEN NULL ELSE orders * 100.0 / visits END. Een rij met nul kiest de NULL-tak. orders * 100.0 / NULLIF(visits, 0) drukt dezelfde regel voor een ontbrekend percentage korter uit.

De eenvoudige CASE-expressie zet de ene waarde om in een andere. Een CASE-expressie met voorwaarden test voorwaarden; een eenvoudige CASE vergelijkt dezelfde expressie met een reeks waarden.

Zet codes om met een eenvoudige CASE

CASE status
  WHEN 'S' THEN 'Shipped'
  WHEN 'P' THEN 'Pending'
  WHEN 'C' THEN 'Canceled'
  ELSE 'Other'
END AS status_label

De impliciete tests zijn status = 'S', daarna status = 'P' en daarna status = 'C'. Een status buiten deze codes krijgt 'Other'. De vorm met voorwaarden kan dezelfde gelijkheidstests expliciet schrijven. Kies een CASE met voorwaarden voor bereiken, LIKE-patronen, NULL-tests of voorwaarden over meerdere kolommen.

CASE region WHEN NULL THEN 'missing' ELSE region END kan een NULL-regio niet opvangen. De impliciete vergelijking region = NULL is UNKNOWN, dus de rij komt bij ELSE en blijft NULL. Schrijf CASE WHEN region IS NULL THEN 'missing' ELSE region END.

Ken een aangepast sorteervolgnummer toe

De alfabetische prioriteitsvolgorde is high, low, medium. Om op urgentie te sorteren, zet je de labels om in getallen:

SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'high' THEN 1
           WHEN 'medium' THEN 2
           WHEN 'low' THEN 3
           ELSE 4
         END;

De oplopende volgorde plaatst nu high vóór medium en medium vóór low, met andere of ontbrekende waarden achteraan. De CASE is een sorteerexpressie en hoeft geen weergegeven kolom te zijn. DESC zou de numerieke rangschikking omkeren.

Maak een numerieke indicator

CASE WHEN discount_pct > 0 THEN 1 ELSE 0 END AS is_discounted

Een positief percentage krijgt 1; een bekende nul krijgt 0. Een NULL-percentage krijgt ook 0 omdat > 0 UNKNOWN is en ELSE dit opvangt. Deze indicator betekent dus “er is een positieve korting geregistreerd”. Hij behoudt het onderscheid tussen geen korting en een onbekende korting niet. Als dat onderscheid nodig is, voeg dan een IS NULL-tak met een eigen resultaat toe.

Je kunt indicatoren selecteren, sorteren, filteren of in berekeningen opnemen. Herhaal in een WHERE-clausule die ook in andere databases werkt de expressie of gebruik de oorspronkelijke voorwaarde in plaats van naar een nieuw gedefinieerde uitvoeralias te verwijzen.

Herken verwante voorwaardelijke functies

COALESCE(bonus, 0) is een korte vorm van CASE WHEN bonus IS NOT NULL THEN bonus ELSE 0 END. NULLIF(a, b) komt overeen met CASE WHEN a = b THEN NULL ELSE a END. Hun namen maken deze gebruikelijke doelen direct duidelijk.

SQLite vanaf 3.32 en SQL Server hebben IIF(condition, true_value, false_value); MySQL heeft IF. DECODE in Oracle zet gelijke waarden om, bijvoorbeeld DECODE(status, 'S', 'Shipped', 'P', 'Pending', 'Other'). DECODE behandelt twee NULL-waarden als gelijk, anders dan de gelijkheidsvergelijkingen in een eenvoudige CASE. Standaard-CASE werkt in al deze systemen.

Een CASE kan een andere CASE bevatten, maar gecombineerde voorwaarden maken een enkele expressie vaak duidelijker. Een verzendregel kan rechtstreeks WHEN country = 'US' AND subtotal >= 100 THEN 0 bevatten, zodat de tests voor land en subtotaal bij elkaar blijven.

Een rapport kan in één SELECT namen opschonen, nettototalen berekenen, maanden aanduiden en de verzendsnelheid een label geven. Bouw elke expressie op vanuit de invoer en controleer het resultaat voordat je haar met de andere combineert.

Werk met bekende rijen

Een ordertabel bevat deze records. Datums zijn ISO-tekst voor de SQLite-query hieronder; quantity is een geheel getal en unit_price is numeriek.

Hier betekent een ontbrekende korting dat er geen korting is. Begin met het ophalen van de oorspronkelijke velden. Voeg telkens één berekende kolom toe en gebruik LIMIT 5 om een kleine steekproef te controleren.

Volg een geneste berekening

ROUND(quantity * unit_price *
      (1 - COALESCE(discount_pct, 0) / 100.0), 2)

COALESCE levert eerst een bekende korting. Delen door 100.0 behoudt het deel achter de komma. Aftrekken van 1 geeft het betaalde deel, vermenigvuldiging geeft het nettobedrag en ROUND bepaalt de uiteindelijke precisie. Ana's bedrag is 33,75, dat van Ben is 240,0 en dat van Chen is 51,0. De NULL-korting van Chen wordt nul; een ontbrekende verzenddatum moet daarentegen ontbreken in het aantal dagen.

Stel het SQLite-rapport samen

SELECT order_id,
       first_name || ' ' || last_name AS customer,
       ROUND(quantity * unit_price *
         (1 - COALESCE(discount_pct, 0) / 100.0), 2) AS net_total,
       strftime('%Y-%m', order_date) AS order_month,
       CAST(julianday(shipped_date) - julianday(order_date)
         AS INTEGER) AS days_to_ship,
       CASE
         WHEN shipped_date IS NULL THEN 'not shipped'
         -- fast = shipped within 2 days
         WHEN julianday(shipped_date) - julianday(order_date) <= 2
           THEN 'fast'
         ELSE 'standard'
       END AS shipping
FROM orders
ORDER BY order_id;

Elke expressie heeft een duidelijke uitvoeralias. Het numerieke dagverschil wordt naar INTEGER geconverteerd omdat dit waarden met alleen een datum zijn. De IS NULL-tak komt vóór de vergelijking van de snelheid, zodat een niet-verzonden order een bruikbaar label krijgt.

De uitvoer is:

Pas typen en queryclausules aan

Gebruik voor DATE-kolommen in PostgreSQL TO_CHAR(order_date, 'YYYY-MM') voor de maand en shipped_date - order_date voor het aantal dagen en de CASE-vergelijking. Houd unit_price als exact numeric voor ROUND met twee argumenten. De concatenatie, vervangingswaarde voor korting en indelingsregel behouden dezelfde betekenis.

Om in beide databases te filteren op afgeronde totalen boven 50, herhaal je de volledige netto-expressie in WHERE. Orders 1002 en 1003 blijven over. SQLite staat ook WHERE net_total > 50 toe, maar PostgreSQL kan die nieuwe alias tijdens het filteren niet zien. ORDER BY net_total DESC werkt in beide.

Controleer bij het verplaatsen van een rapport de typen en functienamen in elke expressie. Een tekstfunctie moet tekst ontvangen, een datumberekening moet een ondersteunde datumwaarde ontvangen en een numerieke vervangingswaarde moet de bedoelde regel uitdrukken. Zet elke uitvoer op een eigen regel; een kort commentaar kan een bedrijfsgrenswaarde definiëren zonder de SQL onduidelijk te maken.