Как выделить текст с разделителями по отдельности и полностью столбец таблицы SQL Server

Question

У меня есть таблица SQL Server с колонкой p_author содержащие точку с запятой (;) текстовое значение с разделителями. Я использовал этот запрос для разделения и выбора соответствующих идентификаторов из другой таблицы, но он разделяется только один раз, тогда как мне нужны все идентификаторы значений после разделения p_author содержит одно значение, два значения, три значения или любое количество значений. Under - это используемый запрос для разделения и выбора соответствующих идентификаторов из другой таблицы.

    select aid as [CountedID] 
    from sub_aminer_author 
    where name like (select RIGHT(p_author, LEN(p_author) - CHARINDEX(';', p_author)) 
                     from sub_aminer_paper 
                     where pid = 4)  

Пример данных показан здесь на этом изображении.

@ DarkKnight - это мой вывод в SqlServer2014

Answer 1

Попробуй это..

DECLARE @X XML
    DECLARE @STR VARCHAR(MAX)=''
                Select @STR = @STR+';'+P_AUTHOR
                From sub_aminer_paper
                WHERE PID = 4
                ORDER BY PID

  select @STR = substring(@STR,2,len(@STR))

    SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@STR,';','</s> <s>') + '</s>   </root> ')

     select aid as [CountedID], name
        from sub_aminer_author s
        inner join (
        SELECT row_number() over(order by (select null)) as rn, T.c.value('.','varchar(max)') as value
        FROM @X.nodes('/root/s') T(c)) t on t.value = s.name
        order by rn

Пример скрипки: http://sqlfiddle.com/

Answer 2

Как было сказано в комментариях, вам действительно нужно удалить этот плохой дизайн.

Это приведет только к большим проблемам и проблемам с производительностью. (Я знаю, что когда-нибудь вам придется иметь дело с ужасным замыслом на некоторое время)

Между тем, если вам действительно нужно разделить строки, можно использовать этот тип запроса с рекурсивным CTE ( SQL Fiddle):

create table sub_aminer_author(pid int, p_author varchar(max), name varchar(max));
go
Insert into sub_aminer_author(pid, p_author, name) values 
(1, 'AAAA;BBBBB;CCCCC', 'AAAA'), (2, 'DDDDD;EEEEE;FFFF;GGGGGGGG', 'GGGGGGGG'), (3, 'HHH', 'GGGGGGGG');
go

with split(pid, first, pos) as(
    Select pid, cast(1 as bigint)
        , Case When CHARINDEX(';', p_author) > 0 
        Then CHARINDEX(';', p_author)-1 Else len(p_author) End
    From sub_aminer_author
    Union All
    Select d.pid, s.pos+2
        , Case When CHARINDEX(';', d.p_author, s.pos+2) > 0 Then CHARINDEX(';', d.p_author, s.pos+2)-1 Else len(p_author) End
    From split s
    Inner Join sub_aminer_author d on d.pid = s.pid
    Where s.pos < len(d.p_author)-1
) 
Select d.pid, s.first, s.pos    , SUBSTRING(d.p_author, s.first,  s.pos - s.first +1) 
From split s
Inner Join sub_aminer_author d on d.pid = s.pid 
order by d.pid, s.first

Вы должны понимать, что это не хорошо и не эффективно. Все должно быть в порядке, если только вы временно используете его, чтобы исправить текущую проблему дизайна и переместить разделенные данные в лучший дизайн.