3
votes

MySQL 5.5 - Comment extraire une partie d'un texte d'un champ avec des chaînes répétitives?

J'ai des champs comme celui-ci:

newValue oldValue
5        1
51       11

et j'essaie d'extraire le texte après le premier du côté gauche. Ce sera soit un ou deux chiffres / lettres. Aussi, en même temps, je veux obtenir d'abord en regardant de gauche à droite. SO les résultats sont:

UPDATE</transactionType><column><name>prio</name><newValue>5</newValue><oldValue>1</oldValue><newValue>aaa<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>
UPDATE</transactionType><column><name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>

mysql

3 commentaires

MySQL 8.0 a REGEXP_SUBSTR () mais si vous utilisez 5.5, vous feriez mieux de simplement récupérer la chaîne entière dans votre application et de l'analyser.

aaa et bbb n'ont pas de balise de fermeture. Vérifiez 12.11 Fonctions XML . Essayez db-fiddle .

@Kalenji n'est-ce qu'une partie d'un XML ou vous avez exactement ce que vous collez ici?

4 Réponses :

1
votes

Comme je le comprends, vous ne collez qu'une partie de votre champ xml. S'il s'agit d'un xml valide, vous pouvez utiliser une expression xpath par la fonction ExtractValue

Je vais vous donner un exemple simple:

Définition des données

select ExtractValue(title, '/a/b[1]') from Test;

Query

create table Test(id integer, title varchar(2000));
insert into Test(id, title) values(1, "<a><b>X</b><b>Y</b></a>");

Cette requête renvoie le premier élément b à l'intérieur de la balise a (note le " 1 dans la requête). Le résultat dans ce cas est X .

Dans votre cas, vous pouvez donc utiliser deux fonctions ExtractValue en une seule requête pour sélectionner la première balise newValue et la première balise oldValue. P >

Lien: Fonction ExtractValue dans la documentation MySQL

1 commentaires

Problème potentiel: "NULL est retourné si xml_frag contient des éléments qui ne sont pas correctement imbriqués ou fermés et qu'un avertissement est généré ..."

1
votes

Comme ce n'est pas du XML correct, essayons quelques outils de chaîne de bas niveau.

mysql> SELECT SUBSTRING_INDEX(
                  SUBSTRING_INDEX(
'UPDATE</transactionType><column><name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>',
                                  '</newValue>', 1),
                              '<newValue>', -1) AS x;
+----+
| x  |
+----+
| 51 |
+----+
1 row in set (0.00 sec)

Explication:

Le SUBSTRING_INDEX intérieur obtient la sous-chaîne via le premier .
Le SUBSTRING_INDEX extérieur obtient le texte après le premier

Essayez-le sur d'autres chaînes.

Cela devrait fonctionner pour toute version de MySQL datant d'au moins une décennie.

devrait fonctionner de la même manière, et peut être une deuxième 'colonne' dans SELECT.

0 commentaires

1
votes

La fonction

SUBSTRING_INDEX est utile dans certains contextes. Pour les valeurs «bien formées» qui contiennent les balises spécifiées, nous pouvons obtenir un résultat utilisable. Mais cette approche s'effondre (renvoie des résultats potentiellement inattendus) lorsque les valeurs ne sont pas bien formées.

SELECT CASE WHEN t.foo LIKE '%<oldValue>%</oldValue>%' THEN 
         SUBSTRING_INDEX(SUBSTRING_INDEX( t.foo ,'</oldValue>',1),'<oldValue>',-1)
       END AS first_oldValue
     , CASE WHEN t.foo LIKE '%<newValue>%</newValue>%' THEN
         SUBSTRING_INDEX(SUBSTRING_INDEX( t.foo ,'</newValue>',1),'<newValue>',-1) 
       END AS first_newValue
  FROM ( SELECT 'UPDATE</transactionType><column><name>prio</name><newValue>5</newValue><oldValue>1</oldValue><newValue>aaa<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>' AS foo 
         UNION ALL 
         SELECT 'UPDATE</transactionType><column><name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>'
       ) t

Nous pouvons incorporer les vérifications d'intégrité dans les expressions de la liste SELECT

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(  t.foo  ,'</oldValue>',1),'<oldValue>',-1) AS first_oldValue
     , SUBSTRING_INDEX(SUBSTRING_INDEX(  t.foo  ,'</newValue>',1),'<newValue>',-1) AS first_newValue
  FROM ( SELECT 'UPDATE</transactionType><column><name>prio</name><newValue>5</newValue><oldValue>1</oldValue><newValue>aaa<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>' AS foo 
         UNION ALL 
         SELECT 'UPDATE</transactionType><column><name>prio</name><newValue>51</newValue><oldValue>11</oldValue><newValue>bbb<oldValue>10863321</oldValue></column></row></table></businessObjectChanges>'
       ) t
 WHERE t.foo LIKE '%<oldValue>%</oldValue>%'
   AND t.foo LIKE '%<newValue>%</newValue>%'

0 commentaires

1
votes

Comme il s'agit d'un XML incomplet, utilisons des fonctions de chaîne simples.

LOCATE peut trouver la position d'une sous-chaîne.

LEFT obtient une sous-chaîne du début à une position.

Et à partir de cette sous-chaîne, le SUBSTRING_INDEX La fonction est pratique pour obtenir les caractères après la balise finale.

Exemple de code:

SELECT  
REGEXP_SUBSTR(col,'(?<=<oldValue>)[^<>]*(?=</oldValue)',1,1) as oldValue,
REGEXP_SUBSTR(col,'(?<=<newValue>)[^<>]*(?=</newValue>)',1,1) as newValue
FROM YourTable;

Résultat:

XXX

Un test sur rextester ici p>

Remarque:

Dans MySql 8, vous pouvez également utiliser REGEXP_SUBSTR pour cela.

oldValue    newValue
1           5
11          51

Un test sur db fiddle here

(Mais gardez le silence à ce sujet. Certains vous désapprouveraient pour avoir utilisé des regex pour analyser XML. Fe ici .
Mais là encore, un XML invalide n'est pas vraiment un XML)

0 commentaires