Извлечение 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 указаны идентификаторы полей, которые указаны в следующем столбце, разделенные знаком «:».

Я хотел бы извлечь определенные поля из второго столбца на основе идентификатора/положения из столбца 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

Проблема в том, что я не знаю, как объявить столбец как переменную, чтобы я мог извлечь необходимое поле для каждой отдельной строки таблицы.

Желаемый результат для столбца [Sample] должен выглядеть следующим образом:

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

Или если Открыто для пользовательской функции

Взгляните на TSQL/SQL Server - табличная функция для анализа/разделения строки с разделителями на несколько/отдельные столбцы

EDIT — Обновление для примера

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. Однако, если вы примените его к столбцу Sample следующим образом: 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
@Ятросин протестирован на Образце. Какой разделитель используется для выборки - person John Cappelletti; 04.01.2018
comment
Точно так же :. Я также попробовал эту функцию, но не знаю, как применить ее ко всему столбцу. Я сгенерировал функцию разделения и использовал следующую команду Select * from [dbo].[Split](Table1.[Sample],':'). Спасибо - person Yatrosin; 04.01.2018
comment
@Yatrosin У вас есть образцы данных ... отлично ... возможно, если вы отредактируете свой вопрос и предоставите желаемые результаты. - person John Cappelletti; 04.01.2018
comment
@Yatrosin См. РЕДАКТИРОВАТЬ / Обновить - 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 Хорошо, я не понимаю, что и где порядок строк здесь играет роль. - 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
@Ytrosin Хорошо, кажется, я понял. Анализ работает, но ваши результаты не соответствуют исходной последовательности. Из-за параллелизма нет gtd порядка записи, если он не указан с помощью ORDER BY. Если ваши исходные данные не имеют правильной последовательности, такой как отметка Identity или DateTime, вам может не повезти. - person John Cappelletti; 04.01.2018
comment
Хорошо, я понимаю. Все равно спасибо - person Yatrosin; 04.01.2018