Hello!

declare @a table
(
value varchar(30)
)

declare @b table
(
[key] int,
[text] varchar(30)
)

declare @c table
(
[key] int,
[contains] varchar(30)
)

declare @tab_aux table
(
[key] int,
value varchar(30)
)

insert into @a values
('aaa'),
('bbb'),
('ssdd'),
('qay')

insert into @b values
(1,'xxxaaayyyqay'),
(3,'assdd');

insert into @tab_aux
select 
[key],
value
from @a
cross apply @b
where
PATINDEX('%'+value+'%',[text]) <> 0


SELECT DISTINCT[key],
       STUFF((    SELECT ',' + B.Value AS [text()]
                          FROM @tab_aux B
                         WHERE B.[Key] = A.[Key]
                         FOR XML PATH('')
                     ), 1, 1, '' ) AS [contains]
FROM  @tab_aux A

• Proposed as answer by pituachModerator 22 hours 28 minutes ago

I have 2 tables in a SQL Server database: Table A(Value), which contains some strings in the column "Value" Table B(Key,Text),  which also contains strings (in the column "Text") Now I want to find all rows in B which contain at least one string of A  and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)

The solution I am looking for may not use a Cursor and  may not use the CONTAIN-Statement (fulltextsearch feature)

try this..

create table [Table] (key1 int,Text varchar(200))
create table [Table2] (value varchar(200))
insert into [table] 
values (1,'xxaaayyyqay'),(2,'wertfg'),(3,'assdd'),(4,'dfdfdf'),(5,'abbbbaa'),(6,'ymmmaaaggbbbb'),(7,'qayfffaaaopop')
insert into [table2] values ('aaa'),('qay'),('ssdd'),('opop'),('mmm'),('ddd'),('bbbb')
go
create function dbo.fntest (@value varchar(200))
RETURNS TABLE 
AS
RETURN 
(
select [Key1],@value as value from [table] A where Text like '%'+@value+'%'
)
GO
;with CTE AS (select key1,T.value from Table2 T cross apply dbo.fntest(value))

select distinct key1,
STUFF((Select ','+value
from CTE T1
where T1.key1=T2.key1
FOR XML PATH('')),1,1,'') as value from CTE T2


drop function fntest
drop table [table]
drop table table2

Hello!

declare @a table
(
value varchar(30)
)

declare @b table
(
[key] int,
[text] varchar(30)
)

declare @c table
(
[key] int,
[contains] varchar(30)
)

declare @tab_aux table
(
[key] int,
value varchar(30)
)

insert into @a values
('aaa'),
('bbb'),
('ssdd'),
('qay')

insert into @b values
(1,'xxxaaayyyqay'),
(3,'assdd');

insert into @tab_aux
select 
[key],
value
from @a
cross apply @b
where
PATINDEX('%'+value+'%',[text]) <> 0


SELECT DISTINCT[key],
       STUFF((    SELECT ',' + B.Value AS [text()]
                          FROM @tab_aux B
                         WHERE B.[Key] = A.[Key]
                         FOR XML PATH('')
                     ), 1, 1, '' ) AS [contains]
FROM  @tab_aux A