Strings en stringfuncties in MySQL
1. Strings
2. Veld types
3. String functies
4. Strings vergelijken
Er is redelijk veel onwetendheid in verband met MySQL, de meeste amateur-site bouwers kennen enkel een paar simpele queries om hun gegevens snel uit de database te halen.
MySQL is veel meer dan dat, veel functies die vaak in php gebruikt worden, zijn sneller uit te voeren in MySQL.
Ik neem aan dat je de basis van MySQL kent, en dat je een recente versie van MySQL hebt. Ik zal proberen te schrijven welke functie waarvoor werkt.
Deze tutorial geeft een beeld van de mogelijkheden. Meer mogelijkheden vind je terug in de manual van MySQL.org. Achter elke hoofdtitel zet ik een referentie naar de pagina waar je de info terugvind.
1. Strings (ref.)
Een string is een aantal lettertekens, omring met aanhalingstekens (dubbel of enkel)
Vrij vertaald, mag je een zin zeggen. (Opgepast: 123 is een nummer (integer) en "123" is een string!)
MySQL kent sommige tekens een speciale betekenis toe. Om deze speciale betekenis te ontwijken, zet je voor het teken in kwestie een backslash ( ) als escape character. MySQL gebruikt volgende tekens:
| Teken |
Betekenis |
| |
het cijfer nul (ASCII 0) |
| ' |
enkel aanhalingsteken |
| " |
dubbel aanhalingsteken |
|
backspace |
|
|
nieuwe lijn |
|
|
een enter (carriage return) |
| |
tab |
|
Control-Z (ASCII 26) dit teken staat voor het einde van het bestand in windows (EOF) |
| \ |
back-slash |
| \% |
procent teken |
| \_ |
liggend streepje (underscore) |
Deze tekens zijn hoofdlettergevoelig dwz. 'B' zal gewoon een 'B' afdrukken, en geen backspace!
Sinds MySQL 4.1 worden character sets ondersteund. Dit betekend dat je verschillende schrijften kan gebruiken (latijns, sirilisch, arabisch, ...). Dit kan nuttig zijn als je websites in meerdere talen bouwt (hou er bv. rekening mee dat het latijns schrift in vele Europese landen anders is: franse accenten, duitse estzet, noorse eu, enz.)
Voor concrete informatie hieromtrent, kun je best naar de MySQL documentatie kijken (ref.).
 top
2. Veld types (ref.)
Ik ga even kort de verschillende veld types bespreken, vooral in verband met hun gebruik.
1.1 CHAR en VARCHAR
Deze twee velden zijn de meest courante vormen om strings op te slagen.
Het grootste verschil tussen CHAR en VARCHAR is dat CHAR altijd het aantal bytes inneemt voor het aantal karakters die in de velddefinitie staat, ongeacht hoeveel karakters je erin opslaagt. VARCHAR daarentegen gebruikt storage space afhankelijk van het aantal tekens dat je erin zet, plus een byte om de lengte te onthouden.
Alle spaties aan het einde van de string zullen wegvallen.Als je probeert van een te lange string in deze veldtypes te bewaren, zullen enkel de eerste tekens genomen worden.
Over het algemeen kun je dus beter VARCHAR boven CHAR verkiezen, dit houdt je tabel klein en dus zullen je opzoekingen sneller gebeuren.
1.2 BINARY en VARBINARY
Deze velden zijn ongeveer hetzelfde als CHAR en VARCHAR, maar zijn binair.
Het grote voordeel hiervan is dat bij opzoekingen 'a' niet meer gelijk is aan 'A'.
Voor MySQL 4.1.2 bestaan deze veldtypen niet als veldtypen, maar konden ze bij de creatie gespecifiëerd worden.
1.3 BLOB en TEXT
Deze twee velden zijn erop voorzien om grote hoeveelheden data te bevatten, met als verschil dat BLOB binaire data kan bevatten, en TEXT niet.
Het aantal tekens die je in de velden kunt bewaren is afhankelijk of je een tiny, medium of long gebruikt.
Aangezien deze velden zo groot zijn, kun je snelheidsproblemen ondervinden bij zoeken of bij de GROUP BY.
Om dit te verhelpen, kun je de SUBSTRING() functie gebruiken (zie later) of de servervariabele max_sort_length (ref. Server System Variables)
1.4 ENUM en SET
Hoewel deze twee types weinig met elkaar te maken, neem ik ze samen om ze heel kort te bespreken.
ENUM is een soort array; waarin je waarden kan onthouden. (ref.)
SET is een voorgemaakte lijst van waarden, je zou het kunnen vergelijken met een drop down list. (ref.)
top
3. String functies (ref.)
Hier komen we bij het echte werk, de string functies.
Ik heb mij gebaseerd op de manual op MySQL.com.
Verschillend dan de meeste programeertalen, staat de eerste letter van een string op positie één!3.1 ASCII(str)
Geeft de numerieke waarde van het eerste teken van de string str. Indien het leeg is, wordt er een 0 teruggegeven.
mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100 |
3.2 BIT_LENGTH(str)
Geeft de lengte van de string str in bits.
mysql> SELECT BIT_LENGTH('text');
-> 32 |
BIT_LENGTH() is toegevoegd in MySQL 4.0.2.
3.3 CHAR(N,...)
CHAR() interpreteerd de argumenten als integers (zonder kommas) en geeft de stringwaarde van deze integers terug. NULL waarden worden overgeslagen.
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM' |
3.4 CONCAT(str1,str2,...)
Geeft een string terug waarvan alle argumenten na elkaar tegen elkaar geplakt zijn. Indien één van de waarden NULL is, wordt automatisch NULL geretourneerd.
mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3' |
3.5 CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() staat voor CONCAT With Separator en is een speciale vorm van CONCAT().
Het eerste argument zal als separator dienen tussen (enkel tussen, niet voor of na!) de andere argumenten. Als de separator NULL is, geeft de functie NULL terug, NULL waarden om te concateneren worden overgeslagen
mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> 'First name,Last Name'
|
3.6 FIND_IN_SET(str,strlist)
Zoekt str in strlist, en geeft de positie hiervan weer.
strlist is een lijst met waarden, met een komma verdeeld.
Geeft een 0 indien str niet in strlist is of indien strlist leeg is. Geeft een NULL waarde indien een van beide argumenten NULL is. Indien je een komma in het eerste argument gebruikt, kun je problemen krijgen.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2 |
3.7 HEX(N_or_S)
Indien N_OR_S een nummer is, geeft de functie een string terug die de hexadecimale waarde teruggeeft.
Vanaf MySQL4.0.1 geeft deze functie de hexadecimale waarde terug indien N_or_S een string is.
mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT 0x616263;
-> 'abc'
mysql> SELECT HEX('abc');
-> 616263 |
3.8 INSERT(str,pos,len,newstr)
Voegt newstr in str, beginnende op positie pos en voor de lengte van len karakters.
Indien pos niet in de lengte van de string is, wordt str teruggegeven. Indien de lengte van len groter is dan het einde van str (ongeacht de lengte van str of van newstr) wordt er niets meer getoond.
Indien een van de argumenten een NULL waarde heeft, wordt er NULL teruggestuurd
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
-> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
-> 'QuWhat' |
3.9 INSTR(str,substr)
Retourneerd de positie van substr in str.
Vanaf MySQL 2.23 is deze functie hoofdletter gevoelig. Vanaf MySQL 4.0 is dit maar hoofdletter gevoelig indien een van beide argumenten een binaire string is.
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0 |
3.10 LCASE(str)
LCASE()is een sinoniem voor LOWER().
3.11 LEFT(str,len)
Geeft de len meest linkse tekens uit str terug.
mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba' |
3.12 LENGTH(str)
Geeft de lengte in bytes van str terug.
mysql> SELECT LENGTH('text');
-> 4 |
3.13 LOCATE(substr,str)
LOCATE(substr,str,pos)
De eerste manier geeft de positie waar substr zich voor de eerste keer toont in str. De tweede syntax doet hetzelfde, beginnende op positie pos.
Geeft een 0 terug indien substr niet in str voorkomt.
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7 |
Vanaf MySQL 2.23 is deze functie hoofdletter gevoelig. Vanaf MySQL 4.0 is dit maar hoofdletter gevoelig indien een van beide argumenten een binaire string is.
3.14 LOWER(str)
Zet de string om met alle letters in kleine letters (volgens huidige character set).
mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically' |
3.15 LPAD(str,len,padstr)
Voegt padstr toe aan str, door hem ervoor te plaatsen. De gehele string wordt verkort tot de lengte van len.
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('hi',1,'??');
-> 'h'
|
3.16 LTRIM(str)
Verwijdert all spaties aan het begin van de string str.
mysql> SELECT LTRIM(' barbar');
-> 'barbar' |
3.17 MAKE_SET(bits,str1,str2,...)
Geeft een waarde terug gelijk aan de bit-ste waarde.
NULL waarden worden genegeerd.
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
-> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> '' |
3.18 MID(str,pos,len)
MID(str,pos,len) is een sinoniem voor SUBSTRING(str,pos,len).
3.19 OCT(N)
Geeft de string representatie van de octale waarde (8-talig talstelsel) van N.
mysql> SELECT OCT(12);
-> '14'
|
3. 20 OCTET_LENGTH(str)
OCTET_LENGTH() is een sinoniem voor LENGTH().
3.21 POSITION(substr IN str)
POSITION(substr IN str) is een sinoniem voor LOCATE(substr,str).
3.22 QUOTE(str)
Geeft een resultaat dat kan gebruikt worden als juist ge-escapte data voor een SQL statement. De string str wordt omgeven door enkele aanhalingstekens, en elke enkele aanhalingsteken, backslash NUL (ASCII 0) en Control-Z (ASCII 26) worden ge-escaped.
Deze functie werd toegevoegd in MySQL 4.0.3.
mysql> SELECT QUOTE('Don't!');
-> 'Don't!'
mysql> SELECT QUOTE(NULL);
-> NULL |
3.23 REPEAT(str,count)
Herhaalt count keer str. Indien count<=0 wordt er een lege string teruggegeven. Indien een van beide waardes NULL is, wordt NULL teruggegeven.
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL' |
3.24 REPLACE(str,from_str,to_str)
Geeft de string str terug met met alle from_str verwisseld door to_str.
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com' |
3.25 REVERSE(str)
Retourneerd de string str omgekeerd.
mysql> SELECT REVERSE('abc');
-> 'cba' |
3.26 RIGHT(str,len)
Geeft de len meest rechtse letters uit str terug.
mysql> SELECT RIGHT('foobarbar', 4);
-> 'rbar' |
3.27 RPAD(str,len,padstr)
Retourneerd de string str met padstr rechts-toegevoegd. Indien de gehele string langer is dan len tekens wordt de string verkort.
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
-> 'h' |
3.28 RTRIM(str)
Geeft de string terug zonder de spaties aan het einde (rechts) van de string str.
mysql> SELECT RTRIM('barbar ');
-> 'barbar' |
3.29 SPACE(N)
Geeft N spaties terug.
mysql> SELECT SPACE(6);
-> ' ' |
3.30 SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
Vormen zonder een len argument, geven een deel van string str terug, beginnend op pos tot het einde terug.
De vormen met een lengte argument, geven een deel van string str beginnend op positie pos van len tekens lang.
De vormen die FROM gebruiken zijn standaard SQL syntax.
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica' |
3.31 SUBSTRING_INDEX(str,delim,count)
Retourneerd een deel van string str tot op de count-ste keer dat de delim delimiter is.
Indien count positief is, wordt er van links vertrokken, indien count negatief is wordt er rechts vertrokken.
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com' |
3.32 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
TRIM(remstr FROM] str)
TRIM verwijdert de spaties, standaard wordt er aangenomen dat het BOTH is. Je mag enkel BOTH (beide), LEADING (voorafgaande) of TRAILING (achternakomende) gebruiken.
Indien remstr niet gespecifieerd is wordt er een spatie aangenomen.
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx' |
3.33 UCASE(str)
UCASE() is een sinoniem voor UPPER().
3.34 UNHEX(str)
Doet het tegenovergestelde van HEX(str).
Het interpreteert elk paar hexadecimale tekens in het argument als nummers en converteert ze naar de letter die het cijfer voorsteld.
De resultaten worden teruggegeven als binaire string.
mysql> SELECT UNHEX('4D7953514C');
-> 'MySQL'
mysql> SELECT 0x4D7953514C;
-> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
-> 'string'
mysql> SELECT HEX(UNHEX('1267'));
-> '1267' |
UNHEX() werd toegevoegd in MySQL 4.1.2.
3.35 UPPER(str)
Retourneert de string met alle lettertekens in hoofdletters volgens de huidige character set.
mysql> SELECT UPPER('Hej');
-> 'HEJ' |
top
4. Strings vergelijken
MySQL converteert nummers en strings automatisch.
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test' |
Indien je absoluut een nummer tot een string wilt omvormen, gebruik je de cast() functie
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
-> 38.8, '38.8' |
De functies
expr LIKE pat [ESCAPE 'escape-char']
Pattern matching, met gebruik van de simpele regular expressions vergelijking.
Geeft een 1 voor waar en een 0 voor false. Indien een van beide argumenten (expr of pat).
Met LIKE kun je volgende twee tekens gebruiken:Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL. With LIKE you can use the following two wildcard characters in the pattern:
| Teken |
Betekenis |
| % |
matcht eenderwelk aantal tekens, ook geen tekens |
| _ |
Matcht exact één teken |
mysql> SELECT 'David!' LIKE 'David_';
-> 1
mysql> SELECT 'David!' LIKE '%D%v%';
-> 1 |
Indien je voor deze tekens wilt zoeken in je string, gebruik je een escape character. Indien je geen escape character specifieert, dan neemt MySQL aan dat je een backslash wilt gebruiken ('')
mysql> SELECT 'David!' LIKE 'David\_';
-> 0
mysql> SELECT 'David_' LIKE 'David\_';
-> 1 |
Om een verschillende escape character te gebruiken, gebruik je de ESCAPE als volgt:
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
-> 1 |
Het vergelijken is hoofdletter ongevoelig behalve als een van de twee velden binair is:
mysql> SELECT 'abc' LIKE 'ABC';
-> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
-> 0 |
In MySQL, kan LIKE gebruikt worden op numerieke velden. (dit is een extensie van de standaard SQL LIKE.)
mysql> SELECT 10 LIKE '1%';
-> 1 |
Nota: Aangezien MySQL de C escape syntax gebruikt in strings (oa. '
' om een nieuwe lijn te duiden) moet je elke '' verdubbelen dat je in je LIKE strings gebruikt. Bijvoorbeeld, om te zoeken naar een '
' moet je dit schrijven als een '\n'. Om te zoeken naar een '' moet je er 4 schrijven: '\\'. Dit omdat de backslashes eens door de parser gestript worden (er blijven er dan nog maar twee over) en een tweede keer wanneer de 'pattern matching' gebeurt.
expr NOT LIKE pat [ESCAPE 'escape-char']
Dit is gelijk aan NOT (expr LIKE pat [ESCAPE 'escape-char']).
expr REGEXP pat
expr RLIKE pat
Doet aan pattern matching (vergelijking met symbool-tekens), van expr tegenover pat. De pattern (pat) kan een extended regular expression zijn.
Geeft een 1 als het gematched wordt, geeft een 0 als er geen gelijkenis is. Indien een van beide (expr of pat) gelijk is aan NULL dan is het resultaat ook NULL.
Opmerkingen: Uit compatibiliteitsredenen is RLIKE een synoniem voor REGEXP. MySQL gebruikt de C syntax voor het escapen van tekens (zie uitleg hierboven). REGEXP is enkel hoofdlettergevoelig voor binaire strings.
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*
*line' REGEXP 'new\*.\*line';
-> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0
mysql> SELECT 'a' REGEXP '^[a-d]';
-> 1 |
expr NOT REGEXP pat
expr NOT RLIKE pat
Dit is gelijk aan NOT (expr REGEXP pat).
STRCMP(expr1,expr2)
STRCMP() retourneerd 0 indien de strings gelijk zijn en -1 het eerst argument kleiner is dan het tweede (volgens de huidige sorteer volgorde).
1 wordt in alle andere gevallen geretourneerd
mysql> SELECT STRCMP('text', 'text2');
-> -1
mysql> SELECT STRCMP('text2', 'text');
-> 1
mysql> SELECT STRCMP('text', 'text');
-> 0 |
Vanaf MySQL 4.0 is STRCMP() enkel hoofdlettergevoelig indien een van beide argumenten binair is. Voor MySQL 4.0 was STRCMP() altijd hoofdlettergevoelig.
top
Dit was het voor deze tut. Ik hoop dat jullie weer veel nuttigs hebben bijgeleerd. Als er nog vragen zouden zijn kan je die altijd aan mij stellen via een PM, of gewoon op het forum.
|
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
