Databases / SQL
17. Scalaire functies Je hebt al regelmatig gewerkt met het SELECT-statement. In dit hoofdstuk ga je een aantal scalaire functies bekijken.
17.1.
Wat zijn scalaire functies?
Scalaire functies worden gebruikt om bewerkingen uit te voeren. Een scalaire functie heeft als invoer nul, één of meer zogenaamde parameters. De waarde van een scalaire functie is afhankelijk van de waarden van de parameters. Het probleem van scalaire functies (kortweg "functies") is echter dat ze van platform tot platform verschillend kunnen zijn. Zo zal je bijvoorbeeld op een MySQL platform andere functies tegenkomen dan op een SQLServer platform. De voorbeelden worden toegepast op de Northwind database.
17.2.
Werken met datums
In deze paragraaf ga je met een aantal functies werken die betrekking hebben op datum en tijd. Bij deze functies wordt soms gebruik gemaakt van de toevoegingen Cast en Convert. Je kunt de onderstaande link gebruiken voor meer informatie: http://msdn.microsoft.com/en-us/library/ms187928.aspx
GETDATE() De Getdate-functie geeft de systeemdatum van de server. Je moet gebruik maken van het Select-statement om dit zichtbaar te maken. SELECT GETDATE() Zo is het ook mogelijk om het verschil tussen twee datums te laten uitrekenen: Neem de onderstaande code over: SELECT orderdate - GETDATE() FROM orders Voer deze code uit en je krijgt bijvoorbeeld het onderstaande resultaat:
Versie 4
Blz. 15
Databases / SQL
Het resultaat is anders dan je had verwacht. SQL laat het verschil niet in dagen zien maar met een datum. Om dit probleem op te vangen moet je gebruik maken van Cast. Pas de query nu zo aan zoals hieronder is weergegeven en voer nogmaals de code uit. SELECT CAST(orderdate - GETDATE() AS INT) FROM orders Je krijgt nu de juiste resultaten. Het aantal dagen tussen de bijeenkomstdatum en de huidige datum. Omdat alle bijeenkomst hebben plaatsgevonden in 2005 en 2006 zijn alle getallen negatief. DAY() Met de functie DAY() kun je de dag van de maand uit een datum halen. SELECT DAY(orderdate), orderdate FROM orders MONTH() Met de functie MONTH() kun je het maandnummer uit een datum halen. SELECT MONTH(orderdate), orderdate FROM orders DATENAME(Weekday, ...) De functie datename(weekday, ...) levert de naam van de dag van de week op. SELECT DATENAME(WEEKDAY, orderdate), orderdate FROM orders
YEAR() haalt het jaartal uit een gegeven datum Versie 4
Blz. 16
Databases / SQL
CONVERT Door gebruik te maken van de toevoeging Convert, is het mogelijk om de opmaak van de datum aan te passen. Je zult wel gemerkt hebben dat de datumopmaak afhankelijk is van de landinstellingen van het operating system. Toch wil je vaak dat de datum altijd maar op één bepaalde manier wordt weergegeven. Hieronder zijn een aantal voorbeelden weergegeven. Voor meer opties, kun je de link van blz. 74 gebruiken. Neem de onderstaande code over en voer deze uit: SELECT CONVERT(VARCHAR(20),GETDATE(),103) SELECT CONVERT(VARCHAR(20),GETDATE(),106) SELECT CONVERT(VARCHAR(20),GETDATE(),112) Je krijgt nu het onderstaande resultaat te zien:
Doormiddel van de code krijgt dezelfde datum steeds een andere opmaak. Hieronder staan een aantal codes met de bijbehorende opmaak.
Versie 4
Code
Standard
Input/Output (3)
0 or 100 (1, 2)
Default
mon dd yyyy hh:miAM (or PM)
101
U.S.
mm/dd/yyyy
102
ANSI
yy.mm.dd
103
British/French
dd/mm/yyyy
104
German
dd.mm.yy
105
Italian
dd-mm-yy
Blz. 17
Databases / SQL Code
Standard
Input/Output (3)
106 (1)
-
dd mon yy
107 (1)
-
Mon dd, yy
108
-
hh:mi:ss
9 or 109 (1, 2)
Default + milliseconds
mon dd yyyy hh:mi:ss:mmmAM (or PM)
110
USA
mm-dd-yy
111
JAPAN
yy/mm/dd
112
ISO
yymmdd yyyymmdd
13 or 113 (1, 2)
Europe default + milliseconds
dd mon yyyy hh:mi:ss:mmm(24h)
114
-
hh:mi:ss:mmm(24h)
20 or 120 (2)
ODBC canonical
yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (2)
ODBC canonical (with milliseconds)
yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4)
ISO8601
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7)
ISO8601 with time zone Z.
yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
17.3.
130 (1, 2)
Hijri (5)
dd mon yyyy hh:mi:ss:mmmAM
131 (2)
Hijri (5)
dd/mm/yy hh:mi:ss:mmmAM
Werken met tekst
De datumfuncties zijn uitgebreid besproken. Bij de overige functies zal alleen de syntax en de functie worden weergegeven.
LEN()
Versie 4
Blz. 18
Databases / SQL
Geeft de lengte van een alfanumerieke waarde. SELECT LEN(companyname), companyname FROM customers LEFT()
Met de Left-functie haal je een gegeven aantal karakters uit een string, van links te beginnen. SELECT LEFT(companyname,3), companyname FROM customers LOWER()
Met de Lower-functie converteer je een stringwaarde naar kleine letters. SELECT LOWER(companyname) from customers LTRIM()
Met de Ltrim-functie verwijder je eventuele voorloopspaties uit een string. SUBSTRING
Met de Substring-functie haal je uit een string, vanaf een gegeven positie een aantal karakters. SELECT SUBSTRING(companyname, 2, 3), companyname FROM customers RIGHT()
Identiek aan de Left-functie maar haalt nu de karakters uit een string van rechts te beginnen. SELECT RIGHT(companyname,4), companyname FROM customers RTRIM()
Verwijderd eventuele naloopspaties uit een gegeven stringwaarde.
Versie 4
Blz. 19
Databases / SQL SPACE()
Geeft een rij met spaties. Het aantal spaties is gelijk aan de waarde van de numeriek parameter. SELECT companyname + SPACE(2) + city FROM customers STR()
Converteert een numerieke waarde naar een alfanumerieke waarde. In SQLServer kan de STR-functie ook gebruikt worden om getallen af te ronden (ze leveren wel een string waarde op). SELECT STR(orderid/10, 5, 2) FROM Orders UPPER()
Converteert een gegeven stringwaarde naar hoofdletters. SELECT UPPER(companyname) FROM customers CAST()
Je kunt een stringwaarde converteren naar een numerieke waarde m.b.v. de cast-functie. SELECT CAST('13.5' AS FLOAT)
Versie 4
Blz. 20
