Comment puis-je convertir efficacement des valeurs séparées par des virgules en lignes dans Oracle SQL ?

Stratégie de conversion de valeurs séparées par des virgules dans Oracle SQL

L'organisation des données dans des enregistrements de base de données sous forme de valeurs séparées par des virgules crée des défis lors de la recherche d'une représentation tabulaire. Cet article explore plusieurs techniques SQL permettant de convertir efficacement ces données en lignes afin de faciliter l'extraction et la manipulation des données.

Problème : valeurs séparées par des virgules dans les enregistrements de la base de données

Considérez la structure de tableau suivante, où la colonne de valeurs contient des valeurs séparées par des virgules :

<code>CREATE TABLE tbl1 (
  id NUMBER,
  value VARCHAR2(50)
);

INSERT INTO tbl1 VALUES (1, 'AA, UT, BT, SK, SX');
INSERT INTO tbl1 VALUES (2, 'AA, UT, SX');
INSERT INTO tbl1 VALUES (3, 'UT, SK, SX, ZF');</code>

L'objectif est de convertir ces données dans un format tabulaire, chaque valeur étant séparée dans sa propre ligne :

<code>ID | VALUE
-------------
1  | AA
1  | UT
1  | BT
1  | SK
1  | SX
2  | AA
2  | UT
2  | SX
3  | UT
3  | SK
3  | SX
3  | ZF</code>

Méthode 1 : utilisez REGEXP_SUBSTR et connectez-vous par

Une solution consiste à profiter des clauses REGEXP_SUBSTR et CONNECT BY d'Oracle :

<code>SELECT DISTINCT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, level) ) VALUE, LEVEL
FROM tbl1
CONNECT BY REGEXP_SUBSTR(value, '[^,]+', 1, LEVEL) IS NOT NULL
ORDER BY id, LEVEL;</code>

Cette méthode utilise une expression régulière (REGEXP_SUBSTR) pour extraire chaque sous-chaîne, tandis que CONNECT BY parcourt de manière récursive les valeurs séparées par des virgules.

Méthode 2 : CTE utilisant l'union récursive

Une autre technique consiste à utiliser une expression de table commune (CTE) avec une union récursive :

<code>WITH t (id, res, val, lev) AS (
  SELECT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, 1)) RES, VALUE AS VAL, 1 AS LEV
  FROM tbl1
  WHERE REGEXP_SUBSTR(VALUE, '[^,]+', 1, 1) IS NOT NULL
  UNION ALL
  SELECT id, TRIM(REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1)) RES, VAL, LEV + 1 AS LEV
  FROM t
  WHERE REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1) IS NOT NULL
)
SELECT id, res, lev
FROM t
ORDER BY id, lev;</code>

Cette méthode utilise un CTE récursif pour diviser les valeurs séparées par des virgules en éléments individuels.

Méthode 3 : Utilisation de la méthode récursive d'INSTR

La troisième solution utilise un CTE récursif avec la fonction INSTR pour identifier la position de début et de fin de chaque sous-chaîne :

<code>WITH t (id, value, start_pos, end_pos) AS
  (SELECT id, VALUE, 1, INSTR(VALUE, ',')
  FROM tbl1
  UNION ALL
  SELECT id,
    VALUE,
    end_pos                    + 1,
    INSTR(VALUE, ',', end_pos + 1)
  FROM t
  WHERE end_pos > 0
  )
SELECT id,
  SUBSTR(VALUE, start_pos, DECODE(end_pos, 0, LENGTH(VALUE) + 1, end_pos) - start_pos) AS VALUE
FROM t
ORDER BY id,
  start_pos;</code>

Cette méthode utilise INSTR pour déterminer de manière récursive la position de chaque sous-chaîne et les extraire en conséquence.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!