Comment puis-je diviser efficacement une valeur de colonne unique contenant plusieurs segments de données en colonnes distinctes dans une base de données ?-tutoriel mysql-php.cn

Comment puis-je diviser efficacement une valeur de colonne unique contenant plusieurs segments de données en colonnes distinctes dans une base de données ?

Patricia Arquette

Libérer： 2024-12-17 10:41:24

original

498 Les gens l'ont consulté

How can I efficiently split a single column value containing multiple data segments into separate columns in a database?

Diviser une valeur de colonne unique en plusieurs colonnes

Dans les bases de données, une tâche courante consiste à diviser une valeur de colonne unique qui contient plusieurs éléments de informations dans des colonnes distinctes. Cet article présente une approche en ligne pour réaliser cette transformation des données.

Énoncé du problème

Nous avons un tableau d'abonnements où le numéro d'abonnement est stocké sous forme de valeur unique. dans une colonne. Le numéro d'abonnement se compose de plusieurs segments séparés par des tirets et des espaces. L'objectif est de diviser cette valeur en colonnes individuelles, telles que le préfixe, le segment 1, le segment 2, etc.

Exemple

Considérez l'exemple d'abonnement suivant numéros :

SC 5-1395-174-25P
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&P
SC ABCD-2525-120

Copier après la connexion

Solution

Voici une requête en ligne qui effectue le fractionnement :

Declare @YourTable table (SomeCol varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')


Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.SomeCol,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Copier après la connexion

Résultat

Le résultat de la requête est le suivant :

+---------+---------+---------+---------+---------+---------+---------+
| COL1    | COL2    | COL3    | COL4    | COL5    | COL6    | COL7    |
+---------+---------+---------+---------+---------+---------+---------+
| SC      | 5       | 1395    | 174     | 25P     | NULL    | NULL    |
| SC      | 1       | 2134    | 123     | ABC     | C1      | 2       |
| SC      | 12      | 5245    | 1247    | 14&P    | NULL    | NULL    |
| SC      | ABCD    | 2525    | 120     | NULL    | NULL    | NULL    |
+---------+---------+---------+---------+---------+---------+---------+

Copier après la connexion

Explication

La requête utilise une application croisée pour créer un ensemble de lignes pour chaque segment dans le numéro d'abonnement. L'expression de chemin XML '/x[1]' extrait le premier segment, '/x[2]' extrait le deuxième segment, et ainsi de suite. Les fonctions ltrim et rtrim sont utilisées pour nettoyer tout espace de début ou de fin.

Approche alternative

Vous pouvez également créer une nouvelle table à la volée pour stocker la division. valeurs :

Declare @YourTable table (PUB_FORM_NUM varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Select A.PUB_FORM_NUM
      ,B.*
 Into  MyNewPubTable
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Select * From MyNewPubTable

Copier après la connexion

Conclusion

Les valeurs fournies L'approche en ligne est une solution polyvalente pour extraire plusieurs segments à partir d'une seule valeur de colonne. Il peut être facilement adapté pour répondre à vos besoins spécifiques en matière de données.

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!