Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Силли
Guest
Ребята, подскажите пожалуйста!

Есть запись вида "a1;b2;", которую нужно разбить на составляющие, сделать лукап по связанной таблице и вернуть в одной строке значения лукапов вида "foo,bar". Попытка переделать входную строку в параметр для условия IN ни к чему не привёл, вернее, у меня это не работает. Дальше-то собрать всё в одну строку при помощи PATH() я смогу.
А вообще так в идеале должна получиться функция, типа WebListLabels(@S VARCHAR(30)) returns VARCHAR(255).

declare @WEBLIST table(
  WL_CODE varchar(3),
  WL_LABEL varchar(30)
);

insert into @WEBLIST (WL_CODE, WL_LABEL) values ('a1', 'foo');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('b2', 'bar');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('c3', 'test');


declare @S VARCHAR(255);

set @S = 'a1;b2;';

if left(reverse(@S), 1) = ';'
 set @S = left(@S, len(@S) - 1);

set @S = '''' + replace(@S, ';', ''',''') + '''';

-- print (@S);

select * from @WEBLIST where WL_CODE in (@S);

-- предполагалось что должно вернуть то же, что и этот скрипт:
-- select * from @WEBLIST where WL_CODE in ('a1','b2');


Может поделитесь какими ценными идеями?
8 фев 16, 20:50    [18790104]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Glory
Member

Откуда:
Сообщений: 104751
Силли
Может поделитесь какими ценными идеями?

Поиск по форуму по слову парсинг
8 фев 16, 21:03    [18790154]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Силли
Guest
Glory, спасибо за совет, это уже большой шаг вперёд. Был найден пример, на основе которого сделан вот этот код, который работает просто прекрасно:

declare @WEBLIST table(
  WL_CODE varchar(3),
  WL_LABEL varchar(30)
);

insert into @WEBLIST (WL_CODE, WL_LABEL) values ('a1', 'foo');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('b2', 'bar');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('c3', 'test');


declare @S VARCHAR(255);
set @S = 'a1;b2;';

select @S = 'select ''' + replace(@S, ';', ''' as val union all select ''') + '''';
declare @T table(ID int identity, VAL varchar(30));
insert into @T exec(@S);

select stuff((select ',' + WL_LABEL from @WEBLIST left outer join @T on WL_CODE = VAL where VAL != '' for xml path('')), 1, 1, '');


Попытка сделать из этого функцию привела к таким результатам:

create function WebListLabels(@S VARCHAR(30)) returns VARCHAR(255) as
begin
  select @S = 'select ''' + replace(@S, ';', ''' as val union all select ''') + '''';
  declare @T table(ID int identity, VAL varchar(30));
  insert into @T exec(@S);
  RETURN stuff((select ',' + WL_LABEL from WEBLIST left outer join @T on WL_CODE = VAL where VAL != '' for xml path('')), 1, 1, '');
end;


Выдаёт такую ошибку:
автор
Msg 443, Level 16, State 14, Procedure WebListLabels, Line 5
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.


Хорошо, если этот код не работает, решено было пробовать код от 2005 версии сервера, хоть код и начал выдавать ошибку при отсутствии разделителя в конце строки. Но всё же так всё работает:
declare @S varchar(255);
set @S = 'a1;b2;';

  with T as (
    select 1 ID, convert(varchar(max), @S) as str, convert(varchar(max), '') VAL
    union all
    select ID + 1, 
    convert(varchar(max), stuff(str, 1, charindex(';', str), '')), 
    substring(str, 0,  charindex(';', str))
    from T
    where VAL is not null
  )
  select stuff((
    select ',' + WL_LABEL from T
    left outer join WEBLIST on WL_CODE = VAL
    where VAL != '' order by ID
  for xml path('')), 1, 1, '');


Только подскажите пожалуйста, как завернуть это в функцию?
create function WebListLabels(@S VARCHAR(30)) returns VARCHAR(255) as
begin
  declare @result varchar(255);
  with T as (
    select 1 ID, convert(varchar(max), @S) as str, convert(varchar(max), '') VAL
    union all
    select ID + 1, 
    convert(varchar(max), stuff(str, 1, charindex(';', str), '')), 
    substring(str, 0,  charindex(';', str))
    from T
    where VAL is not null
  )
  set @result = stuff((
    select ',' + WL_LABEL from T
    left outer join WEBLIST on WL_CODE = VAL
    where VAL != '' order by ID
  for xml path('')), 1, 1, '');
  return @result;
end;

Здесь ругается на "set @result =", если вместо этого просто делать return - тот же результат. Пере "with" тоже не получается...
8 фев 16, 22:16    [18790412]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
aleks2
Guest
Тредстартер сумел выбрать самую проблемную реализацию парсинга.
Это явный талант.
Пожелаем ему поскорее нарваться на SQL-injection.
9 фев 16, 05:54    [18790891]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Силли
Guest
aleks2, в первой реализации - да, возможно применить sql-injection. Но во второй-то где? И меня она устраивает, только не знаю, как вернуть результат - работает только select, возвращающий таблицу... Может Вы подскажете?
Ну или ткните меня лицом в правильную реализацию парсинга, но чтобы либо без with (а то не знаю, как через него получить на выходе что-то отличное от таблицы), либо скажите, как получить строку на выходе...
9 фев 16, 09:39    [18791185]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Силли,

Функция, которая делит строку на слова
9 фев 16, 10:02    [18791305]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Glory
Member

Откуда:
Сообщений: 104751
declare @WEBLIST table(
  WL_CODE varchar(3),
  WL_LABEL varchar(30)
);

insert into @WEBLIST (WL_CODE, WL_LABEL) values ('a1', 'foo');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('b2', 'bar');
insert into @WEBLIST (WL_CODE, WL_LABEL) values ('c3', 'test');


declare @S VARCHAR(255);
set @S = 'a1;b2;';


select stuff((select ',' + WL_LABEL from @WEBLIST where @s like '%'+WL_CODE+';%'for xml path('')), 1, 1, '');
9 фев 16, 10:12    [18791360]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
Силли
Но во второй-то где? И меня она устраивает, только не знаю, как вернуть результат - работает только select, возвращающий таблицу...
create function WebListLabels(@S VARCHAR(30)) returns VARCHAR(255) as
begin
  declare @result varchar(255);
  with T as (
    select 1 ID, convert(varchar(max), @S) as str, convert(varchar(max), '') VAL
    union all
    select ID + 1, 
    convert(varchar(max), stuff(str, 1, charindex(';', str), '')), 
    substring(str, 0,  charindex(';', str))
    from T
    where VAL is not null
  )
  select @result = stuff((
    select ',' + WL_LABEL from T
    left outer join WEBLIST on WL_CODE = VAL
    where VAL != '' order by ID
  for xml path('')), 1, 1, '');
  return @result;
end;
9 фев 16, 10:19    [18791419]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Силли
Guest
Glory, уложиться в одну строчку, ух ты! Это гениально! Спасибо огромное!!
invm, благодарю! Действительно, select всё решает.
9 фев 16, 11:13    [18791714]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Glory
Member

Откуда:
Сообщений: 104751
Силли
уложиться в одну строчку

зато потенциально это самый медленный вариант.
И чем больше объемы, тем медленнее.
9 фев 16, 11:15    [18791727]     Ответить | Цитировать Сообщить модератору
 Re: Получить список значений разделённого списка ("a1;b2;" =>"foo;bar")  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
КО говорит, что SQL сервер не должен заниматься парсингом и прочей ересью.
9 фев 16, 11:43    [18792000]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить