Mengekstraksi bidang string ke-n yang dibatasi oleh : disimpan dalam kolom SQL

Saya memiliki tabel SQL dengan dua kolom berikut:

FORMAT  Sample
GT:AD:DP:GQ:PL  0/0:233,0:233:99:0,120,1800
GT:AD:DP:GQ:PL  0/1:101,61:220:99:835,0,1859
GT:AD:DP:GQ:PL  0/0:172,0:172:99:0,120,1800
GT:AD:DP:GQ:PL  0/0:216,0:216:99:0,120,1800
GT:AD:DP:GQ:PL  0/0:216,0:216:99:0,120,1800
GT:AD:DP:GQ:PGT:PID:PL  0/1:185,232:417:99:0|1:8029494_T_G:8670,0,6429
GT:AD:DP:GQ:PL  0/0:367,0:367:99:0,120,1800
GT:AD:DP:GQ:PGT:PID:PL  0/1:150,198:348:99:0|1:8029494_T_G:7930,0,5677
GT:AD:DP:GQ:PGT:PID:PL  0/1:148,196:344:99:0|1:8029494_T_G:7876,0,5652
GT:AD:DP:GQ:PGT:PID:PL  0/0:148,0:344:99:0|1:8029494_T_G:7876,8334,14591
GT:AD:DP:GQ:PGT:PID:PL  0/0:148,0:344:99:0|1:8029494_T_G:7876,8334,14591

Kolom FORMAT menentukan ID untuk bidang yang diberikan di kolom berikut dibagi dengan ":".

Saya ingin mengekstrak bidang tertentu dari kolom kedua berdasarkan ID/posisi dari kolom FORMAT, yaitu AD (2), DP (3) atau GQ (4).

Saya dapat mengekstrak bidang AD dengan kode berikut:

SELECT SUBSTRING(Sample, CHARINDEX(':',Sample)+1, CHARINDEX(':',Sample,5)-5) FROM Table 1;

Masalahnya adalah saya tidak dapat mengekstrak bidang DP atau GQ, karena panjang bidang yang berbeda tidak selalu sama dan saya tidak dapat menentukan posisi awal mana untuk mencari lokasi ":" berikut.

Saya juga mencoba menggunakan fungsi Split dari situs web ini:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Masalahnya adalah saya tidak tahu cara mendeklarasikan kolom sebagai variabel sehingga saya bisa mengekstrak bidang yang diperlukan untuk setiap baris tabel.

Output yang diinginkan untuk kolom [Sample] akan terlihat seperti ini:

GT  AD  DP  GQ
0/0 233,0   233 99
0/1 101,61  220 99
0/0 172,0   172 99
0/0 216,0   216 99
0/0 216,0   216 99
0/1 185,232 417 99
0/0 367,0   367 99
0/1 150,198 348 99
0/1 148,196 344 99
0/0 148,0   344 99
0/0 148,0   344 99

Bantuan apa pun akan dihargai,

Terima kasih,


string sql-server substring split charindex
person Yatrosin    schedule 04.01.2018    source sumber
comment
charindex() bukan fungsi MySQL. Dugaan saya adalah Anda menggunakan SQL Server.   -  person Gordon Linoff    schedule 04.01.2018

Jawaban (1)


arrow_upward
1
arrow_downward

Mungkin sedikit XML sebagai parsernya

Contoh

Select A.Format
      ,B.*
 From  YourTable A
 Cross Apply (
                Select 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)')))
                From  (Select Cast('<x>' + replace((Select replace(A.Format,':','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Pengembalian

Format                  Pos2    Pos3    Pos4
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PL          AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ
GT:AD:DP:GQ:PGT:PID:PL  AD      DP      GQ

Atau versi Sederhana

Select A.Format
      ,Pos2 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)')
      ,Pos3 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
      ,Pos4 = Cast('<x>' + replace(Format,':','</x><x>')+'</x>' as xml).value('/x[4]','varchar(max)')
 From  YourTable A

Atau jika Terbuka untuk UDF

Intip TSQL/SQL Server - fungsi tabel untuk mengurai/memisahkan string yang dibatasi menjadi beberapa/kolom terpisah

EDIT - Pembaruan untuk Sampel

Select A.Format
      ,GT = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[1]','varchar(max)')
      ,AD = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)')
      ,DP = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
      ,GQ = Cast('<x>' + replace(Sample,':','</x><x>')+'</x>' as xml).value('/x[4]','varchar(max)')
 From  YourTable A
person John Cappelletti    schedule 04.01.2018
comment
Itu berfungsi bila diterapkan pada kolom FORMAT. Namun jika diterapkan pada kolom Sample seperti ini: Select Table1.[Sample] ,Pos1 = Cast('<x>' + replace([Sample],':','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)') ,Pos2 = Cast('<x>' + replace([Sample],':','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)') ,Pos3 = Cast('<x>' + replace([Sample],':','</x><x>')+'</x>' as xml).value('/x[4]','varchar(max)') From Table1, hasilnya benar-benar aneh dan tidak konsisten - person Yatrosin; 04.01.2018
comment
@Yatrosin diuji pada Sampel. Apa pembatas yang digunakan untuk sampel - person John Cappelletti; 04.01.2018
comment
Persis sama :. Saya juga mencoba fungsinya, tetapi saya tidak tahu bagaimana menerapkannya ke seluruh kolom. Saya membuat fungsi Split dan saya menggunakan perintah berikut Select * from [dbo].[Split](Table1.[Sample],':'). Terima kasih - person Yatrosin; 04.01.2018
comment
@Yatrosin Anda memiliki data sampel ... bagus ... mungkin jika Anda mengedit pertanyaan Anda dan memberikan hasil yang diinginkan. - person John Cappelletti; 04.01.2018
comment
@Yatrosin Lihat EDIT / Perbarui - person John Cappelletti; 04.01.2018
comment
Saya telah mencobanya lagi dan saya melihat apa masalahnya sekarang. Kode ini menampilkan keluaran secara alfanumerik dimulai dengan kolom pertama (GT) dan seterusnya. Saya ingin mempertahankan urutan baris yang asli. Apakah mudah untuk memperbaikinya? - person Yatrosin; 04.01.2018
comment
@John-Cappelletti yang terhormat. Saya telah mencoba mempertahankan urutan asli dengan fungsi ROW_NUMBER() OVER(ORDER BY Table1.[Sample]) , tetapi saya belum dapat memperbaikinya. Saya pikir itu lebih mudah. Bisakah Anda membantu saya memperbaikinya? Terima kasih - person Yatrosin; 04.01.2018
comment
@Yatrosin Sedang menelepon, tapi saya akan melihat kedua dalam 20 menit - person John Cappelletti; 04.01.2018
comment
@Yatrosin Oke, saya tidak jelas apa atau di mana Urutan Baris berperan di sini. - person John Cappelletti; 04.01.2018
comment
Jika saya menerapkan rumus seperti sekarang, saya mendapatkan keluaran berikut, bukan yang diinginkan: GT AD DP GQ 0/0 148,0 344 99 0/0 148,0 344 99 0/0 172,0 172 99 0/0 216,0 216 99 0/0 216,0 216 99 0/0 233,0 233 99 0/0 367,0 367 99 0/1 101,61 220 99 0/1 148,196 344 99 0/1 150,198 348 99 0/1 185,232 417 99 - person Yatrosin; 04.01.2018
comment
@Yatrosin Oke, saya rasa saya mengerti. Penguraian berfungsi, tetapi hasil Anda tidak sesuai urutan aslinya. Karena paralelisme, tidak ada gtd urutan rekaman kecuali ditentukan dengan ORDER BY. Jika data asli Anda tidak memiliki urutan yang tepat seperti stempel Identitas atau TanggalWaktu, Anda mungkin kurang beruntung. - person John Cappelletti; 04.01.2018
comment
OK saya mengerti. Terima kasih - person Yatrosin; 04.01.2018