Mauvaise pratique

Avant de commencer, je tiens à vous signaler que le fait de stocker des données différentes dans un même champs avec une concaténation est une très mauvaise pratique pour ce type de base de données (sauf pour les DBMS supportant JSON en natif ou des types de données complexes come MySQL et PostgreSQL, mais attention tout de même).

Mais comme trop souvent dans notre métier, on hérite de codes précédents pas toujours très propres (je suis gentil en disant ça). Cette technique va donc vous aider à extraire les informations dont vous avez besoin. Et petit conseil : refactorisez au plus vite cette partie de la base de données !

Cadre de notre exemple

Pour faire la démonstration de cette petite astuce, il faut définir un cadre sur lequel réfléchir. Imaginons une table stockant des coordonnées (en latitude et longitude) pour des personnes avec leur nom. La latitude et la longitude sont stockée dans un seul et unique champ, concaténée avec le caractère virgule ,, c’est ainsi que Google réprésente les coordonnées dans Google Map.

Voici le listing de création de la table :

CREATE TABLE friends (name text, coord text);
INSERT INTO friends (name, coord)
VALUES ('Léa', '48.860934, 2.337709'), ('Robert', '  45.732859, 4.818218'),
('Lucie', '43.600989,1.446647'), ('Camille', '44.848670,-0.579957');

Cette table, friends comportent 4 entrées, avec nom et coordonnées. Petit jeux : devinez à quels lieux réels correspondent ces coordonnées.

Première étape

Bien, maintenant que nous avons nos données de base pour tester, allons-y !

L’astuce va consister tout simplement à utiliser une requête intermédiaire pour ajouter un nouveau champ qui contiendra pour chaque entrée la position du caractères de séparation. Pour faire ça, il nous faudra utiliser la fonction instr qui retourne la position d’une chaîne dans une autre.

Cette requête intermédiaire est la suivante ;

SELECT  *,
        instr(coord, ',') AS separator
FROM    friends;

Le nouveau champ separator, contiendra la position du caractère , dans le champ coord et s’ajoute aux champs existants dans le résultat de cette requête SELECT.

La sortie de cette requête donne ça :

Léa         48.860934, 2.337709  10        
Robert        45.732859, 4.8182  12        
Lucie       43.600989,1.446647   10        
Camille     44.848670,-0.579957  10 

Deuxième étape (et dernière)

Nous avons la requête intermédiaire avec le nouveau champ contenant la position du caractère de séparation. Grâce à cette nouvelle donnée, nous pouvons récupérer la partie de la chaîne située avant ce caractère et la partie située après ce caractère.

Pour cela, on utilise bien sûr la fonction substr qui permet d’extraire une chaîne de caractères à partir d’une autre, en connaissant la position de départ et sa longueur.

Dans notre cas :

1. La première partie de la chaîne, qui correspond à la latitude, a un point de départ à 1 et une longueur égale à la position du caractère séparateur moins un (on ne veut pas du caractère séparateur).
2. La deuxième partie de la chaîne, la longitude, commence à la position juste après le caractère séparateur et va jusqu’à la fin (donc pas besoin de préciser ceci à la fonction substr qui ira jusqu’au bout par défaut)

Voici ce que ça donne concrètement :

SELECT  name,
        substr(coord, 1, separator - 1) AS lat,
        substr(coord, separator + 1)    AS lng
FROM (
    SELECT  *,
            instr(coord, ',') AS separator
    FROM    friends
);

Et la sortie doit être normalement comme ceci :

Léa         48.860934    2.337709 
Robert        45.73285   4.818218 
Lucie       43.600989   1.446647  
Camille     44.848670   -0.579957 

Bonus

Hum… Avez-vous remarquez ? J’ai glissé un petit piège dans les données de départ, histoire de simuler un ensemble de données mal formatées.

Pour avoir des données propres, il faut s’assurer de bien éliminer les espaces avant et après les chaînes extraites. Via la fonction trim qui est toujours notre amie sur les jeux de données mal ficelés.

Notre requête devient alors celle-ci :

SELECT  name,
        trim(substr(coord, 1, separator - 1)) AS lat,
        trim(substr(coord, separator + 1))    AS lng
FROM (
    SELECT  *,
            instr(coord, ',') AS separator
    FROM    friends
);

Et maintenant nous avons bien des données propre, sans espaces en trop :

Léa         48.860934   2.337709  
Robert      45.732859   4.818218  
Lucie       43.600989   1.446647  
Camille     44.848670   -0.579957 

Conclusion

Vous savez maintenant comment découper une chaîne dans SQLite !

Pour résumer :

1. Passez par une requête intermédiaire pour récupérer la position du caractère de séparation.
2. Extrayez la première partie de la chaîne
3. Extrayez la deuxième partie de la chaîne
4. Nettoyez éventuellement les chaînes extraites
5. Si possible, refactorisez cette table et adaptez le code exploitant ces données pour éviter tout ça