Wie kann ich einen einzelnen Spaltenwert, der mehrere Datensegmente enthält, effizient in separate Spalten in einer Datenbank aufteilen?

Einen einzelnen Spaltenwert in mehrere Spalten aufteilen

In Datenbanken besteht eine häufige Aufgabe darin, einen einzelnen Spaltenwert aufzuteilen, der mehrere Teile enthält Informationen in separate Spalten. In diesem Artikel wird ein Inline-Ansatz zum Erreichen dieser Datentransformation vorgestellt.

Problemstellung

Wir haben eine Tabelle mit Abonnements, in der die Abonnementnummer als einzelner Wert gespeichert ist in einer Spalte. Die Abonnementnummer besteht aus mehreren Segmenten, die durch Bindestriche und Leerzeichen getrennt sind. Das Ziel besteht darin, diesen Wert in einzelne Spalten aufzuteilen, beispielsweise das Präfix, Segment 1, Segment 2 usw.

Beispiel

Betrachten Sie das folgende Beispielabonnement Zahlen:

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

Lösung

Hier ist eine Inline-Abfrage, die die Aufteilung durchführt:

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

Ergebnis

Die Ausgabe der Abfrage lautet wie folgt:

+---------+---------+---------+---------+---------+---------+---------+
| 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    |
+---------+---------+---------+---------+---------+---------+---------+

Erklärung

Die Abfrage verwendet eine Kreuzanwendung, um eine Reihe von Zeilen für jedes Segment im zu erstellen Abonnementnummer. Der XML-Pfadausdruck „/x[1]“ extrahiert das erste Segment, „/x[2]“ extrahiert das zweite Segment und so weiter. Die ltrim- und rtrim-Funktionen werden verwendet, um alle führenden oder nachfolgenden Leerzeichen zu bereinigen.

Alternativer Ansatz

Sie können auch spontan eine neue Tabelle erstellen, um die Aufteilung zu speichern Werte:

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

Fazit

Die bereitgestellten Der Inline-Ansatz ist eine vielseitige Lösung zum Extrahieren mehrerer Segmente aus einem einzelnen Spaltenwert. Es kann leicht an Ihre spezifischen Datenanforderungen angepasst werden.

Das obige ist der detaillierte Inhalt vonWie kann ich einen einzelnen Spaltenwert, der mehrere Datensegmente enthält, effizient in separate Spalten in einer Datenbank aufteilen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!