W kilku moich projektach potrzebowałem przechowywania wartości listy w pojedynczym polu tabeli bazy danych. By usprawnić operacje na tego typu polu stworzyłem zestaw kilku funkcji, które to ułatwiają.

-- przykłady listy:
-- '1;84;154;157;162;270'
-- 'pl;uk;ru'

create function [dbo].[listAdd](@list varchar(8000), @val varchar(80))
returns varchar(8000)
as
begin
  declare @trimVal varchar(80)

  set @trimVal = ltrim(rtrim(@val))

  -- if not exists add new item to list
  if (dbo.listExists(@list, @val) < 1)
    set @list = concat(@list, ';', @trimVal)

  return (@list)
end;

create function [dbo].[listDelete](@list varchar(8000), @val varchar(80))
returns varchar(8000)
as
begin
  declare @t table (item varchar(80))
  declare @trimVal varchar(80)
  declare @result varchar(8000)

  set @trimVal = ltrim(rtrim(@val))
  set @result = ''

  insert into @t
    select items from dbo.Split(@list, ';')
    where items <> @trimVal

  select @result = @result + ',' + item
    from @t

  set @result = substring(@result, 2, len(@result)-1)
  
  return (@result)
end;

create function [dbo].[listExists](@list varchar(8000), @val varchar(80))
returns int
as
begin
  declare @trimVal varchar(80)
  declare @result int
  declare @item varchar(80)

  set @trimVal = ltrim(rtrim(@val))

  select @item = items 
    from dbo.Split(@list, ';')
       where items = @trimVal

  if (@@ROWCOUNT > 0)
    set @result = 1
  else
    set @result = 0

  return (@result)
end;

create function [dbo].[listCount](@list varchar(8000))
returns int
as
begin
  declare @result int

  select @result = count(items) from dbo.Split(@list, ';')
  if (@@ROWCOUNT < 1)
    set @result = 0

  return (@result)
end;

SELECT PoleListy FROM tabela WHERE dbo.listExists(tabela.PoleListy, 'wartosc')

SELECT PoleListy FROM tabela WHERE dbo.listExists(tabela.PoleListy, 'pl')

SELECT PoleListy FROM tabela WHERE dbo.listExists(tabela.PoleListy, '84')