Préambule
Ce qu’il faut bien comprendre est que SQL n’est pas à proprement parler un langage de programmation mais un langage de description de requête. On décrit uniquement ce que doit être le résultat et le moteur de base de données l’interprète. La rigueur syntaxique nécessaire pour la polyvalence et la praticité d’un langage de programmation n’est pas obligatoire et l’accent est plutôt mis sur l’expressivité pour l’opérateur humain. C’est par exemple pour cela que AND peut avoir plusieurs sens selon ce qui l’entoure1. Inversement R a un comportement très « pur » mais il vous permettra des traitements beaucoup plus complexes.
Une autre particularité est que même si on peut définir des fonctions personnelles en SQL, elles ne sont pas aussi souples que dans un vrai langage de programmation. SQL est avant tout un outil pour manipuler des tables de données.
Par ailleurs, je vais parfois parler de la librairie stringr, son intérêt est triple :
- souvent plus rapide que les fonctions de base
- est basée elle-même sur la librairie stringi, qui gère mieux les encodages
- est plus homogène dans ses appels (la chaîne source est toujours en premier paramètre
mais il existe souvent des équivalents dans les fonctions de base.
Petits rappels préliminaires
La différence entre un opérateur et une fonction n’est pas conceptuelle mais uniquement l’aspect du code à écrire :
L’appel d’un opérateur (par exemple A + B, une opération binaire dans les 2 langages au sens qu’elle utilise 2 membres écrits de chaque côté de l' »opérateur » ) est en réalité l’application de la fonction « + » aux paramètres A et B. On définit 2 familles d’opérateurs en SQL comme en R : les opérateurs unaires et les binaires. En SQL, il existe aussi un opérateur ternaire ... BETWEEN ... AND ...
Les fonctions SQL unaires sont + (inutile la plupart du temps), – (opposé), NOT (inverse logique). Les binaires sont beaucoup plus nombreuses ( +, -, /, *, = (le égal de comparaison), AND, OR, XOR, IS, …)
Quoi qu’il en soit SQL comme R gèrent les fonctions et les opérateurs mais il y a 2 différences principales :
En R, on peut utiliser les paramètres par position ou en les nommant de façon très simple et standard :
#soit la fonction
appel <- function(x, a, b) {...}
#On peut appeler par position :
appel(1, 2, 3)
# au sein de la fonction x = 1, a = 2, b = 3
# ou par nom :
appel(b = 1, a = 2, x = 3)
# au sein de la fonction chaque paramètre correspondra aux noms passés
# dans l'appel
# ou un mix des 2 :
appel(b = 1, 2, 3)
# b sera fixé à 1 puis x prendra la valeur suivante et enfin aEn SQL, les paramètres sont passés de façon standard uniquement par position (l’équivalent du 1er appel). Selon votre moteur de SGBD, d’autres types de passages peuvent être supportés -ou non- avec un gros polymorphisme entre moteurs que je ne pourrai pas couvrir.
Dans la suite de l’article, nous traiterons uniquement de la stricte équivalence de SQL en R en restant le plus proche du SQL « de base ».
Les fonctions de chaines
La taille d’une chaine
Pour récupérer la taille d’une chaine, on utilise LENGTH() ou CHAR_LENGTH() en SQL. C’est un faux ami en R car length() renvoie la longueur (c’est à dire le nombre d’éléments dedans) d’un vecteur. Pour la taille d’une chaine, il faut utiliser nchar().
SELECT chaine, LENGTH(chaine) AS longueur
FROM tbl;
# se traduit par :
tbl %>% mutate(longueur = nchar(chaine))Il existe aussi CHARACTER_LENGTH() qui est synonyme de CHAR_LENGTH() mais pas de LENGTH().
Plus exactement :
| SQL | Signification | R | Note |
| LENGTH() | La taille en octets de stockage | nchar(…, type = « bytes ») | |
| CHAR_LENGTH() CHARACTER_LENGTH() | La taille en nombre de caractères | nchar(…, type = « chars ») | valeur par défaut pour R |
| – | La taille en caractères à l’ecran | nchar(…, type= »width ») |
Très souvent les 3 sont équivalents mais pas si vous utilisez de l’encodage unicode (bytes sera plus long que les autres) ou un encodage autorisant les décorations (width sera plus court).
La plupart du temps, ce que vous cherchez est le comportement par défaut donc vous écrirez juste nchar().
La concaténation
En SQL, selon votre moteur, vous disposez de l’opérateur de concaténation || (double pipe) soit de la fonction CONCAT() (soit des 2…).
Pour ||, c’est un opérateur binaire qui n’attend donc qu’un paramètre de chaque côté mais qu’on peut enchainer2. Tandis que pour CONCAT() la cardinalité est variable (on peut passer autant de valeurs que l’on veut).
-- Version opérateur
SELECT (... d autres champs...), A || " " || B AS nouvelleColonne
FROM tbl;
-- Version fonction
SELECT (... d autres champs...), CONCAT(A, " ", B) AS nouvelleColonne
FROM tbl;En R, nous avons paste() à cardinalité variable et prenant quelques paramètres nommés dont sep= qui peut nous intéresser car il définit le séparateur à intégrer entre les valeurs.
tbl %>% mutate(nouvelleColonne = paste(A, B) # car par défaut sep = " "
tbl %>% mutate(nouvelleColonne = paste(A, " ", B, sep = "") Pour rappel, sep= peut prendre toute valeur que vous jugez pertinente y compris sur plusieurs caractères. Les séparateurs sont placés exclusivement entre les valeurs, jamais en début ou fin de chaine.
Les sous-chaines
L’extraction
En SQL, vous avez SUBSTRING() ou SUBSTR() avec diverses possibilités d’appel :
- Le premier paramètre est toujours la chaine à découper
- Le second paramètre est le début du découpage, en commençant à compter à partir de 1
- Le troisième est facultatif est définit la longueur maximum du résultat
-- Différents modes d'appel
SELECT(SUBSTRING("Bonjour", 1, 3));
SELECT(SUBSTRING("Bonjour" FROM 1 FOR 3));
-- donnent "Bon"
SELECT(SUBSTRING("Bonjour", 4)); # tout à partir de la position
SELECT(SUBSTRING("Bonjour", 4, 4));
SELECT(SUBSTRING("Bonjour" FROM 4 FOR 4));
-- donnent "jour"En R, nous avons substring() ou substr() dans la librairie de base ainsi que d’autres versions comme str_sub() dans la librairie stringr.
substring("Bonjour", 1, 3)
substr("Bonjour", 1, 3)
str_sub("Bonjour", 1, 3)
# donnent "Bon"
# on doit donner le caractère de départ
# et la postion du caractère de fin,
# pas la longueur de la chaine
substring("Bonjour", 4)
substr("Bonjour", 4, 8)
str_sub("Bonjour", 4, 8)
# donnent "Bon"A la différence des 2 premières fonctions, str_sub() permet d’utiliser des débuts et fins vectorisés négatifs pour partir de la fin :
library(stringr)
textes <- c("bonjour", "mon bon !", "jambon")
debuts <- c(1, -5, -3)
fins <- c(3, -3, -1)
str_sub(textes, debuts, fins)
# donne un vecteur de 3 valeurs : c("bon", "bon", "bon")Si vous étiez un fan du BASIC (et d’une saveur de SQL qui a LEFT, MID et RIGHT), vous avez ainsi possibilité d’émuler facilement LEFT() et RIGHT() et MID() si besoin.
le test de presence
Pour tester si les chaînes d’une colonne contiennent un motif, vous utiliseriez INSTR() en sql. Cette fonction retourne 0 en l’absence du motif et sa position si il s’y trouve.
En R, la façon la plus standard de simplement tester la présence est grepl(), fonction de base, alors qu’avec stringr vous utiliserez str_detect(). Ces deux fonctions renvoient TRUE si le motif est trouvé ou FALSE si il est absent. Ce n’est pas strictement superposable mais souvent plus pertinent.
Si vous voulez réellement sa position, vous devrez utiliser la première colonne de regexpr() de la R base ou de str_locate() de stringr :
SELECT (INSTR("YOUPI", "PI")>0) AS present;
-- present = TRUE
SELECT (INSTR("YOUPI", "PLA")>0) AS present;
-- present = FALSE
SELECT INSTR("YOUPI","PLA") AS pos;
-- Non trouvé : 0
SELECT INSTR("YOUPI","PI") AS pos;
-- Trouvé en position 4
En R :
> grepl("PI",c("YOUPI", "YA", "PI"))
[1] TRUE FALSE TRUE
> grepl("PLA", c("YOUPI", "YA", "PI"))
[1] FALSE FALSE FALSE
str_detect(c("YOUPI", "YA", "PI"), "PI")
[1] TRUE FALSE TRUE
str_detect(c("YOUPI", "YA", "PI"), "PLA")
[1] FALSE FALSE FALSE
regexpr("PLA",c("YOUPI", "YA", "PI"))
[1] -1 -1 -1
# Non trouvé : -1
regexpr("PI",c("YOUPI", "YA", "PI"))
[1] 4 -1 1
# Trouvé en position 4, non trouvé, et en 1
str_locate(c("YOUPI","r","PI"),"PI")[,1]
[1] 4 NA 1
# str_locate() renvoit NA si pas trouvé
# regexpr et str_locate() renvoient NA si la chaine source est NACe qui fonctionne tout à fait dans un mutate() :
tbl %>% mutate(present_1 = grepl("PI", chaine),
pos_1 = regexpr("PI", chaine),
present_2 = str_detect(chaine, "PI"),
pos_2 = str_locate(chaine, "PI")[,1])
# A tibble: 4 x 5
chaine present_1 pos_1 present_2 pos_2
<chr> <lgl> <int> <lgl> <int>
1 YOUPI TRUE 4 TRUE 4
2 YA FALSE -1 FALSE NA
3 NA FALSE NA NA NA
4 PI TRUE 1 TRUE 1Notez juste la façon différente de gérer la valeur NA et donc le besoin de préciser [,1] ou [, "start"] pour n’avoir que le début (car sans on obtient le début et la fin de la chaine au sein d’une structure incluse de type array) :
tbl %>% mutate(present_1 = grepl("PI", chaine),
pos_1 = regexpr("PI", chaine),
present_2 = str_detect(chaine,"PI"),
str_locate(chaine, "PI"))
# A tibble: 4 x 5
chaine present_1 pos_1 prensent_2 pos_2[,"start"] [,"end"]
<chr> <lgl> <int> <lgl> <int> <int>
1 YOUPI TRUE 4 TRUE 4 5
2 YA FALSE -1 FALSE NA NA
3 NA FALSE NA NA NA NA
4 PI TRUE 1 TRUE 1 2Le trimming et le padding
Parfois, les données sont constituées ou attendues avec des caractères surnuméraires non significatifs. Les ajouter s’appelle le padding et les enlever le trimming.
Le trimming
En SQL, le trimming se fait avec TRIM() éventuellement complété de LTRIM(), RTRIM()
-- Usage de base
SELECT TRIM(" contenu ") AS t;
-- donne "contenu"
SELECT LTRIM(" contenu ") AS t;
-- donne "contenu "
SELECT RTRIM(" contenu ") AS t;
-- donne " contenu"
-- On peut préciser le caractère à supprimer
SELECT TRIM('.', '...contenu...') AS t; -- "contenu"
-- et on peut utiliser des versions spécifiques
-- selon le moteur bien sûr...
SELECT TRIM(BOTH '.' FROM '...contenu...') AS t; -- "contenu"
SELECT TRIM(LEADING '.' FROM '...contenu...') AS t; -- "contenu..."
SELECT TRIM(TRAILING '.' FROM '...contenu...') AS t; -- "...contenu"Quel que soit le « gout » de votre moteur de SGBD, en R on utilisera trimws() (littéralement « trim whitespace » soit « supprime les espaces blancs »).
trimws() considère par défaut qu’un espace blanc est n’importe quelle combinaison de « espace », « tabulation », « retour à la ligne, « saut de ligne ». On peut régler cela par le paramètre whitespace qui attend une expression régulière, c’est donc un petit piège car certains caractères ont des valeurs expressives intrinsèques comme le point « . » qui signifie tout caractère et doit donc être précédé d’un double antislash :
trimws(" contenu ") # "contenu"
trimws(" contenu ", which = "both") # "contenu"
# both est la valeur par défaut
trimws(" contenu ", which = "left") # "contenu "
trimws(" contenu ", which = "right") # " contenu"
# pour supprimer un autre caractère, il faut simplement
# définir ce qu'est un whitespace :
trimws("...contenu...", which = "both", whitespace ="\\.") # "contenu"
# on a mis \\ car un point seul signifie "tout caractère"
# ce qui engloberait les lettres et donnerait une chaine videLa valeur par défaut est « [ \t\r\n] ». Cette petite complexification permet cependant une démultiplication de la puissance.
Avec la librairie stringr, il existe aussi str_trim() qui ne supporte que l’espace » « .
Le padding
Les fonctions SQL LPAD() ou RPAD() seront remplacées en R par str_pad() de stringr, c’est la solution la plus simple :
SELECT text = LPAD(col, 10, "_")
FROM tbl;
# se traduit par : (en version verbeuse)
tbl %>% mutate(text = str_pad(col, width = 10, side = "left", pad = "_"))
sachant que side= peut être « left » (padding gauche donc alignement à droite), « right » (à gauche) ou aussi « both » (centré).
Changer la casse
R fournit de base toupper() et tolower() pour remplacer respectivement UPPER() et LOWER().
tandis que stringr, propose str_to_upper(), str_to_lower(), mais aussi str_to_title() et str_to_sentence() (ces deux dernières passent toute la chaîne en minuscule sauf la première lettre de chaque mot pour la première, sauf la première lettre de la phrase pour la seconde. Pratique mais attention aux effets de bord.)
Modifier le contenu d’un champ
Pour finir, REPLACE() permet en SQL de remplacer une sous-chaine par une autre. Toutes les occurrences sont remplacées.
SELECT REPLACE(colonne, "jour", "soir") AS nouvelle_colonne
FROM tbl;L’ordre des paramètres est : données source, chaîne à chercher, remplacement.
En R base, vous pouvez utiliser sub() (1 seule occurrence) ou gsub() (toutes les occurrences)
tbl %>% mutate(nouvelle_colonne = gsub("jour", "soir", colonne)Comme vous le voyez l’ordre est : chaîne à chercher, remplacement, données source
Tandis qu’avec stringr, vous utiliserez str_replace() ou str_replace_all() :
tbl %>% mutate(nouvelle_colonne = str_replace_all(colonne, "jour", "soir")Conclusion
Nous avons vu les principales fonctions de traitement de chaînes disponibles en R pour transformer vos requêtes SQL. C’était le plus gros morceau.
La prochaine fois nous aborderons les autres fonctions incontournables.
- En SQL, AND est aussi bien un opérateur binaire comme
x AND yqui retourne vrai (TRUE) uniquement si x et y sont tous les 2 vrais, qu’un morceau de l’opérateur ternairex BETWEEN y AND zqui retourne vrai si ↩︎ A || B || Cest en fait conceptuellement||(A, ||(B,C))mais pour rappel, rien n’oblige le moteur de SGBD de le traiter ainsi ! ↩︎