แยกฟิลด์ที่ n ของสตริงคั่นด้วย : เก็บไว้ในคอลัมน์ SQL

ฉันมีตาราง SQL ที่มีสองคอลัมน์ต่อไปนี้:

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

คอลัมน์ FORMAT ระบุ ID สำหรับฟิลด์ที่กำหนดในคอลัมน์ต่อไปนี้โดยแยกด้วย ///

ฉันต้องการแยกฟิลด์เฉพาะจากคอลัมน์ที่สองตาม ID/ตำแหน่งจากคอลัมน์ FORMAT เช่น AD (ที่ 2), DP (ที่ 3) หรือ GQ (ที่ 4)

ฉันสามารถแยกฟิลด์ AD ด้วยรหัสต่อไปนี้:

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

ปัญหาคือฉันไม่สามารถแยกฟิลด์ DP หรือ GQ ได้ เนื่องจากความยาวของฟิลด์ที่แตกต่างกันนั้นไม่เท่ากันเสมอไป และฉันไม่สามารถระบุได้ว่าตำแหน่งใดควรเป็นตำแหน่งเริ่มต้นในการค้นหาตำแหน่ง /// ต่อไปนี้

ฉันพยายามใช้ฟังก์ชัน Split จากเว็บไซต์นี้ด้วย:

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

ปัญหาคือฉันไม่รู้วิธีประกาศคอลัมน์เป็นตัวแปรเพื่อที่จะสามารถแยกฟิลด์ที่จำเป็นสำหรับทุกแถวของตารางได้

ผลลัพธ์ที่ต้องการสำหรับคอลัมน์ [ตัวอย่าง] ควรมีลักษณะดังนี้:

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

ความช่วยเหลือใด ๆ ที่จะได้รับการชื่นชม

ขอบคุณ,


string sql-server substring split charindex
person Yatrosin    schedule 04.01.2018    source แหล่งที่มา
comment
charindex() ไม่ใช่ฟังก์ชัน MySQL ฉันเดาว่าคุณกำลังใช้ SQL Server   -  person Gordon Linoff    schedule 04.01.2018

คำตอบ (1)


arrow_upward
1
arrow_downward

บางที XML เล็กน้อยเป็นตัวแยกวิเคราะห์

ตัวอย่าง

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

การส่งคืน

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

หรือเวอร์ชันธรรมดา

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

หรือหากเปิดเป็น UDF

ดูที่ เซิร์ฟเวอร์ TSQL/SQL - ฟังก์ชันตารางเพื่อแยกวิเคราะห์/แยกสตริงที่คั่นด้วยหลายคอลัมน์/แยกคอลัมน์

แก้ไข - อัปเดตสำหรับตัวอย่าง

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
ซึ่งใช้งานได้เมื่อใช้กับคอลัมน์ FORMAT อย่างไรก็ตาม หากคุณนำไปใช้กับคอลัมน์ตัวอย่างดังนี้: 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 ผลลัพธ์จะแปลกโดยสิ้นเชิงและไม่สอดคล้องกัน - person Yatrosin; 04.01.2018
comment
@Yatrosin ทดสอบกับตัวอย่าง ตัวคั่นที่ใช้สำหรับตัวอย่างคืออะไร - person John Cappelletti; 04.01.2018
comment
เหมือนเดิมทุกประการ :. ฉันลองใช้ฟังก์ชันนี้ด้วย แต่ฉันไม่รู้ว่าจะนำไปใช้กับทั้งคอลัมน์ได้อย่างไร ฉันสร้างฟังก์ชัน Split และฉันใช้คำสั่งต่อไปนี้ Select * from [dbo].[Split](Table1.[Sample],':') ขอบคุณ - person Yatrosin; 04.01.2018
comment
@Yatrosin คุณมีข้อมูลตัวอย่าง ... เยี่ยมมาก ... บางทีถ้าคุณแก้ไขคำถามและให้ผลลัพธ์ที่ต้องการ - person John Cappelletti; 04.01.2018
comment
@Yatrosin ดูแก้ไข / Upate - person John Cappelletti; 04.01.2018
comment
ฉันได้ลองอีกครั้งแล้วและเห็นว่าปัญหาใดเป็นปัญหาในขณะนี้ รหัสนี้ใช้เอาต์พุตเป็นตัวอักษรและตัวเลขโดยเริ่มจากคอลัมน์แรก (GT) และต่อๆ ไป ผมขอคงลำดับแถวเดิมไว้ครับ ซ่อมง่ายมั้ย? - person Yatrosin; 04.01.2018
comment
ถึง @John-Cappelletti ฉันได้พยายามรักษาลำดับเดิมด้วยฟังก์ชัน ROW_NUMBER() OVER(ORDER BY Table1.[Sample]) แต่ไม่สามารถแก้ไขได้ ฉันคิดว่ามันง่ายกว่า คุณช่วยฉันแก้ไขสิ่งนั้นได้ไหม? ขอบคุณ - person Yatrosin; 04.01.2018
comment
@Yatrosin กำลังโทร แต่ฉันจะดูครั้งที่สองใน 20 นาที - person John Cappelletti; 04.01.2018
comment
@Yatrosin ตกลงฉันไม่ชัดเจนว่า Row Order เข้ามามีบทบาทอะไรหรือที่ไหน - person John Cappelletti; 04.01.2018
comment
หากฉันใช้สูตรตามที่เป็นอยู่ตอนนี้ ฉันจะได้ผลลัพธ์ต่อไปนี้แทนผลลัพธ์ที่ต้องการ: 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 ตกลงฉันคิดว่าฉันเข้าใจ การแยกวิเคราะห์ทำงานได้ แต่ผลลัพธ์ของคุณไม่อยู่ในลำดับดั้งเดิม เนื่องจากการขนานกัน จึงไม่มี gtd ของลำดับการบันทึก เว้นแต่จะระบุด้วย ORDER BY หากข้อมูลต้นฉบับของคุณไม่มีลำดับที่เหมาะสม เช่น การประทับตราข้อมูลประจำตัวหรือวันที่และเวลา คุณอาจโชคไม่ดี - person John Cappelletti; 04.01.2018
comment
โอเค ฉันเข้าใจแล้ว ขอบคุณนะ - person Yatrosin; 04.01.2018