#1 Thu 24 April 2014 12:24
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
création d'un champ issu d'une sous chaine de caractères
Bonjour,
Je travaille sur une base postgre/postgis dans pgadmin et je souhaiterais créer un nouveau champ en le complétant avec une partie d'un champ existant. Je m'explique : j'ai un champ "nom" contenant du texte. Certains de ces enregistrements ont ce champ finissant par un entier placé entre 2 | (ex. : levé bota avril 2014 |124|).
Je voudrais créer un champ me retournant le nombre compris entre | lorsqu'il existe (124 pour mon exemple).
N'étant pas experte en SQL, et après quelques recherches, mon idée est d'utiliser la fonction substr permettant d'extraire une chaine de caractères en indiquant la place du 1er et du dernier. Je crée donc un champ me donnant l'emplacement de mon 1er caractère | (fonction strpos) et un second champ me donnant le nombre de caractère de mon champ nom (char_length). L'idée est d'utiliser le résultat de ces 2 champs en retirant 1 dans la fonction sbstr, et c'est là où je bloque. Comment spécifier qu'il s'agit de la valeur d'un champ dans la syntaxe pour le substr ?
Voilà ce que j'avais penser faire mais le UPDATE n'est pas bon :
CREATE TABLE nouvelletable AS
(
SELECT
nom,strpos (nom,'|'),char_length (nom)
FROM matable
);
ALTER TABLE nouvelletable
ADD COLUMN doc integer;
UPDATE nouvelletable
SET doc = substr (nom,strops-1, char_lenght-1)
WHERE strpos > 0;
Quelqu'un peut-il m'éclairer ?
Merci d'avance
Hors ligne
#2 Thu 24 April 2014 13:02
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
la fonction split_part fera l'affaire :
Code:
UPDATE nouvelletable SET doc = split_part(nom,'|',2);
http://docs.postgresql.fr/9.1/functions-string.html
Dernière modification par MathieuB (Thu 24 April 2014 13:02)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#3 Thu 24 April 2014 13:05
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
Vous pouvez utiliser substr directement dans la requete de creation de la nouvelle table et vous passez de l'update:
La fonction accepte comme argument des valeurs, des colonnes, ou d'autres fonctions, par ex:
Code:
select substr(nom, strlength(nom) -3, 4); CREATE TABLE nouvelletable AS ( SELECT nom, substr(nom, ...) FROM matable );
Cependant, l'ecriture de la formule avec substr, strpos, strlength, etc. pour extraire la valeur entre "|" peut etre complexe et pas très lisible.
Il existe également les fonctions utilisant les expressions régulières qui sont très puissantes pour extraire des parties de chaines de caractères.
Ici, la fonction regexp_matches (http://www.postgresql.org/docs/current/ … tring.html) permet de repondre à la question:
Code:
select regexp_matches('levé bota avril 2014 |124|', E'\\|(.*)\\|');
La fonction renvoie un tableau de toutes les valeurs trouvées par l'expression régulière.
S'il n'y a qu'une série de "|" dans la colonne, alors on peut demander le premier element du tableau:
Code:
select (regexp_matches('levé bota avril 2014 |124|', E'\\|(.*)\\|'))[1];
Le formalisme des expressions régulières n'est pas très aisé, surtout dans PG ou il faut echapper les caractères spéciaux (comme "|") mais ca permet de décrire a peu près tous les cas imaginables.
L'expression ici veut dire (enfin je crois ): "trouve tous les caractères situés entre deux |"
Dans votre cas:
Code:
CREATE TABLE nouvelletable AS ( SELECT nom, (regexp_matches(nom, E'\\|(.*)\\|'))[1] as extrait FROM matable );
Nicolas
Hors ligne
#4 Thu 24 April 2014 13:06
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
la fonction split_part fera l'affaire :Code:
UPDATE nouvelletable SET doc = split_part(nom,'|',2);http://docs.postgresql.fr/9.1/functions-string.html
Ah oui, plus simple !
Hors ligne
#5 Thu 24 April 2014 13:13
Re: création d'un champ issu d'une sous chaine de caractères
Ah oui, plus simple !
Oui mais c'est bien aussi et plus pédagogique de voir les autres fonctions que tu cites !
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#6 Thu 24 April 2014 13:51
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
GENIAL !!!
Merci beaucoup pour vos réponses rapides. Je vais opter pour la facilité avec split_part.
Nicolas, on s'était croisé lors d'une formation ATEN, tu vois je persévère, mais mon utilisation de pgadmin est épisodique et pas si fréquente alors je cherche pas mal. Merci en tout cas pour ta contribution en espérant que tu ne m'en veuilles pas trop d'opter pour l'autre solution ;-)
Hors ligne
#7 Thu 24 April 2014 13:55
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Juste un point encore, dans les cas où il n'y a pas de |, le résultat est '' et non vide. Y a t-il un moyen d'éviter cela ?
Dernière modification par Lydie62 (Thu 24 April 2014 13:55)
Hors ligne
#8 Thu 24 April 2014 15:25
Re: création d'un champ issu d'une sous chaine de caractères
oui, il y a toujours un moyen;-)
Code:
UPDATE nouvelletable SET doc = nullif(split_part(nom,'|',2),'');
Voir en bas de page ici : http://docs.postgresql.fr/9.2/functions … ional.html
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#9 Thu 24 April 2014 16:22
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Merci Mathieu. Ca marche nickel !!!
Je pense en effet qu'il y a toujours une solution mais encore faut-il parler SQL couramment et c'est loin d'être mon cas :-(
Je suis allée voir la doc et du coup j'ai retrouvé juste au-dessus la fonction COALESCE que j'utilise dans un cas de concaténation de champ mais qui me pose un petit soucis également alors si jamais tu as la solution une fois encore.
Je concatène 5 champs texte en utilisant || et j'ai inséré COALESCE pour gérer les vides mais j'aimerais rajouter un séparateur (une virgule par exemple) mais je ne sais comment faire car si je fais ainsi :
UPDATE matable
SET stade = COALESCE (adulte,'') || ',' || COALESCE (juvenile,'') || ',' || COALESCE (ponte,'') || ',' || COALESCE (larve,'') || ',' || COALESCE (exuvie,'') || ' ';
alors je me retrouve avec un tas de virgules.
J'ai essayé la fonction concat_ws mais je n'ai pas réussi. Peit-être que là aussi NULLIF pourrait m'aider. Qu'en penses-tu ?
Après j'arrête mes questions :-)
Dernière modification par Lydie62 (Thu 24 April 2014 16:24)
Hors ligne
#10 Thu 24 April 2014 18:24
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: création d'un champ issu d'une sous chaine de caractères
Bonsoir Lydie,
Oui me souviens
C'est clair que le SQL, ca s'oublie vite !
Dans ton cas, tu veux mettre des virgules qd le champ n'est pas vide uniquement:
Il y a la construction case when ... pour cela, qui permet de faire des tests comme un if then else dans la partie select:
Concatener une virgule si le champ n'est pas vide, sinon concatener une chaine vide.
c'est un peu lourd... :
Code:
... SET statde = COALESCE (adulte,'') || case when length(COALESCE (adulte,'') = 0 then '' else ',' end || COALESCE (juvenile,'') || case when length(COALESCE (juvenile,'') = 0 then '' else ',' end || ...
Nicolas
Hors ligne
#11 Fri 25 April 2014 09:48
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3881
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
Nicolas, est-ce que tu penses que la requête proposée ci-dessous renverrait le même résultat que la tienne?
Code:
... SET stade = COALESCE (adulte,'') || case when length(adulte) = 0 then '' else ',' end || COALESCE (juvenile,'') || case when length(juvenile) = 0 then '' else ',' end || ...
Hors ligne
#12 Fri 25 April 2014 10:13
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour à tous,
j'ai appris récemment (on ne lis jamais assez la doc ;-) ) chez nos amis de forumsig que la fonction concat() de PostgreSQL gère très bien les valeurs nulle. On l'encapsule avec la fonction trim pour supprimer les virgules aux extrémités et voilà.
Code:
select trim(concat(null,',','toto',null,',','titi'),',')
-> toto,titi
le sujet en question : http://www.forumsig.org/showthread.php/ … mps-en-SQL
Dernière modification par MathieuB (Fri 25 April 2014 10:16)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#13 Fri 25 April 2014 10:26
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
Nicolas, est-ce que tu penses que la requête proposée ci-dessous renverrait le même résultat que la tienne?Code:
... SET stade = COALESCE (adulte,'') || case when length(adulte) = 0 then '' else ',' end || COALESCE (juvenile,'') || case when length(juvenile) = 0 then '' else ',' end || ...
Bonjour,
Sans le coalesce, si adulte vaut null, le test length(null) = 0 sera faux et une virgule sera générée, il me sembe.
La solution de Mathieu est la plus clean, de toute facon
Nico
Hors ligne
#14 Fri 25 April 2014 11:38
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3881
Re: création d'un champ issu d'une sous chaine de caractères
Sans le coalesce, si adulte vaut null, le test length(null) = 0 sera faux et une virgule sera générée, il me sembe.
Tu as parfaitement raison
La solution de Mathieu est la plus clean, de toute facon
+1. Faut vraiment que je potasse jusqu'au bout cette doc
Hors ligne
#15 Fri 25 April 2014 14:19
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
Bon j'ai testé la solution de Mathieu mais problème avec la fonction concat (message d'erreur : "Aucune fonction ne correspond au nom donné et aux types d'arguments").
Voilà ma requête :
UPDATE __matable
SET test = trim (concat(null,',',adulte,null,',',juvenile,null,',',ponte,null,',',larve,null,',',exuvie),',');
adulte, juvenile, ponte, larve et exuvie étant l'intitulé des champs à concaténer.
Une nouvelle fois, je sèche :-(
Hors ligne
#16 Fri 25 April 2014 15:09
Re: création d'un champ issu d'une sous chaine de caractères
adulte juvénile ponte larve et exuvie sont des chaînes de caractères ...
A mettre entre guillemets simples ;-)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#17 Fri 25 April 2014 15:45
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
non en fait ce sont les intitulés des champs que je souhaite concaténer
Hors ligne
#18 Fri 25 April 2014 16:41
Re: création d'un champ issu d'une sous chaine de caractères
J'ai lu un peu vite ;-)
Ces colonnes sont-elles bien de type "text" ?
Code:
UPDATE __matable SET test = trim (concat(adulte::text,',',juvenile::text,',',ponte::text,',',larve::text,',',exuvie::text),',');
Dernière modification par MathieuB (Fri 25 April 2014 16:57)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#19 Fri 25 April 2014 19:56
- Benoit91
- Participant assidu
- Date d'inscription: 2 Oct 2008
- Messages: 263
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour
ne serait-ce pas un problème de version de postgresql ? il me semble que cette fonction concat() est disponible depuis la version 9.1
Cordialement.
Hors ligne
#20 Mon 28 April 2014 10:04
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour à tous,
Je me replonge dans mon soucis de concaténation en ce lundi matin. Pour répondre à Mathieu, mes colonnes sont bien au format texte.
C'est peut-être un problème de version car j'ai la version 9.0.2. Dans ce cas comment contourner le problème ?
Hors ligne
#21 Mon 28 April 2014 10:23
Re: création d'un champ issu d'une sous chaine de caractères
Bonjour,
Nous aurions du commencer par la question de Benoit91 ...
2 possibilités dans votre cas : mettre à niveau PostgreSQL ou utiliser l'opérateur de concaténation et COALESCE :
Code:
stade = trim( (COALESCE (adulte,'') || CASE WHEN adulte IS NOT NULL THEN ',' ELSE '' END || COALESCE (juvenile,'') || CASE WHEN juvenile IS NOT NULL THEN ',' ELSE '' END || COALESCE (ponte,'') || CASE WHEN ponte IS NOT NULL THEN ',' ELSE '' END || COALESCE (larve,'') || CASE WHEN larve IS NOT NULL THEN ',' ELSE '' END || COALESCE (exuvie,'') || CASE WHEN exuvie IS NOT NULL THEN ',' ELSE '' END),',')
Dernière modification par MathieuB (Mon 28 April 2014 10:25)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#22 Mon 28 April 2014 10:31
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Désolée, j'aurais du en effet préciser ces infos dès le début. Je vais essayer cette solution et je vous fais un retour. Merci en tout cas pour la réactivité
Hors ligne
#23 Mon 28 April 2014 10:33
Re: création d'un champ issu d'une sous chaine de caractères
Pas de quoi, je voulais dire que nous aurions du poser cette question de la version d'abord ;-)
Dernière modification par MathieuB (Mon 28 April 2014 10:37)
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#24 Mon 28 April 2014 11:22
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Je confirme, ça fonctionne :-)
Seul bémol, pour les cas où il n'y a aucune valeur ça me met '' au lieu de vide
Merci beaucoup à tous pour votre aide
Hors ligne
#25 Mon 28 April 2014 12:22
Re: création d'un champ issu d'une sous chaine de caractères
La fonction NULLIF() résoudra le problème.
http://docs.postgresql.fr/9.0/functions … ional.html
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#26 Mon 28 April 2014 13:47
- Lydie62
- Participant actif
- Date d'inscription: 30 May 2007
- Messages: 86
Re: création d'un champ issu d'une sous chaine de caractères
Parfait avec NULLIF. Merki
Hors ligne