Pft. In SQL worden de zaken soms toch wel onnodig complex gemaakt. Ik was op zoek naar een mogelijkheid om de titels in mijn mediatheek alfabetisch te rangschikken op het eerste betekenisvolle woord.
Zijnde de titels minus de lidwoorden in NL, EN, FR. Het zou zo mooi zijn om dat in 1 ruzie door met regex (regular expressions) op te lossen, maar regex (REGEXP voor MySQL) geeft enkel een waar of vals terug, en geen string.
Maar goed, ik had geen zin om een vergelijking en conditionals van zeven lijnen te schrijven, dus ik moest en zou regex gebruiken:
libw_title REGEXP "^(the|an?|l(e|a|es)|une?|het|de|een) "
REGEXP is de functie die gaat zoeken in libw_title, wat dan weer het veld in de database tabel is, waar, u raadt het al, al de titels van mijn mediatheek in opgeslaan zijn.
"^(the|an?|l(e|a|es)|une?|het|de|een) " is de regex zelf. De aanhalingstekens staan er enkel als delimiters (begrenzers), waarbij het belangrijk is op te merken dat er aan het einde nog een spatie is –als woordeinde.
^ betekent dat we beginnen te zoeken aan het begin van een woord; | is ‘ofwel’, en een vraagteken ? zorgt ervoor dat het voorafgaande karakter optioneel is.
De regex zoekt dus naar the; of a of an (an?); of le of la of les (l(e|a|es)); of un of une (une?); of het of de of een. En eigenlijk zou ik daar misschien nog l’ en ’t aan moeten toevoegen, maar goh, ik zie wel of het nodig blijkt.
Oh, en alles case independent.
Het ultieme doel is om een titel zoals The Amazing Adventures Of Kavalier & Clay onder ‘A’ terug te vinden, en geschreven als Amazing Adventures Of Kavalier & Clay, The. Daartoe hebben we net gezocht naar alle titels die met een lidwoord beginnen. Nu moeten we er iets mee doen.
Als we zo’n titel hebben, moet hij herschreven worden, anders blijft de titel zoals hij was (libw_title). Ofte, in SQL:
IF(aan_deze_voorwaarde_voldaan, herschrijf, doe_niks)
Wat we nu al kunnen uitwerken tot:
IF(libw_title REGEXP "^(the|an?|l(e|a|es)|une?|het|de|een) ", herschrijfregel, libw_title)
De herschrijfregel dan: we willen het eerste stukje (het lidwoord) achteraan de rest van de titel plakken. Als de titel met een lidwoord begint, dan weten we al dat het deel voor de eerste spatie (=het einde van het lidwoord), eruit mag geknipt worden.
Eerst identificeren we het gedeelte tot de eerste spatie:
SUBSTRING_INDEX(libw_title," ",1)
SUBSTRING_INDEX zoekt in een bepaald veld (libw_title) naar het voorkomen van een bepaalde string –in ons geval een spatie (" ")– en meer bepaald naar de eerste keer dat die voorkomt (1).
Het ‘leuke’ aan die functie is dat het niet gewoon ‘waar’ of ‘vals’ zegt, maar een string als resultaat terug geeft, in ons geval het lidwoord.
Great, we hebben nu het lidwoord, nu moeten we het nog van de rest van de titel halen, en achteraan plakken.
Van de titel weghalen doen we met TRIM:
TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title)
Haal weg, van de voorkant (LEADING), het lidwoord (SUBSTRING_INDEX(libw_title," ",1)) uit de titel (FROM libw_title).
Yeeha. Nu hebben we de titel zonder het lidwoord (TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title)) én we kennen ook nog het lidwoord (SUBSTRING_INDEX(libw_title," ",1)). We zijn er bijna: nu nog het lidwoord achteraan plakken.
Dat doen we door de titel terug samen te stellen: titel_zonder_lidwoord + een komma en spatie + het lidwoord; via de functie CONCAT.
CONCAT(titel_zonder_lidwoord, een komma en spatie, het lidwoord)
-ofte, volledig uitgeschreven-
CONCAT(TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title), ", ", SUBSTRING_INDEX(libw_title," ",1))
Nog één dingetje voor we onze IF volledig kunnen herschrijven: de spatie tussen het lidwoord en de rest van de titel. Toen we het lidwoord van de titel weghaalden, bleef er wel nog een spatie vooraan aan de titel kleven. Spaties voor- en achteraan een string kunnen worden verwijderd met… TRIM (zonder argumenten). Dus TRIM(" blah ") geeft ons de string "blah". De nieuwe titel krijgen we dan zo:
TRIM(CONCAT(TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title), ", ", SUBSTRING_INDEX(libw_title," ",1)))
Remember, onze IF?
IF(aan_deze_voorwaarde_voldaan, herschrijf, doe_niks)
-herschreven als-
IF(libw_title REGEXP "^(the|an?|l(e|a|es)|une?|het|de|een) ", herschrijfregel, libw_title)
Dit kunnen we nu completeren tot
IF(libw_title REGEXP "^(the|an?|l(e|a|es)|une?|het|de|een) ", TRIM(CONCAT(TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title), ", ", SUBSTRING_INDEX(libw_title," ",1))),libw_title)
En het ganse SQL commando, dat ons de lijst met titels geeft, ziet er als volgt uit:
SELECT *, IF(libw_title REGEXP "^(the|an?|l(e|a|es)|une?|het|de|een) ", TRIM(CONCAT(TRIM(LEADING SUBSTRING_INDEX(libw_title," ",1) FROM libw_title), ", ", SUBSTRING_INDEX(libw_title," ",1))),libw_title) AS sort_title FROM lib_work ORDER BY sort_title ASC;
Schoon hé.
’t Is proper, maar als je met een SQL-implementatie zonder regexen zit, dan krijg je monsterachtigheden zoals hier: http://blog.zog.org/2004/01/vrees_bewaarhei.html.
…o ja, en “l'” zit er nog niet in bij u 🙂
Mijn idee is trouwens dat er niet veel anders op zit dan als ik ooit eens herbegin aan een nieuwe database, een apart veld voor de sorteertitel voorzien, dat dan voor te laten stellen door het inputprogramma, maar nog wijzigbaar te maken bij input.
Met automatische regels geraak je er nooit vrees ik–zelfde verhaal overigens voor auteurs.