Extracting Nth Field Of String Delimited By ":" Stored In A Sql Column

I have a SQL table with the two following columns: 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

Solution 1:

Perhaps a little XML as the parser

Example

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

Returns

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

Or a Simple version

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

Or if Open to a UDF

Take a peek at TSQL/SQL Server - table function to parse/split delimited string to multiple/separate columns

EDIT - Update for Sample

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