(SOLVED) Multiple keyword character matching problem

red8174gf

E.g. keywords 'aaa, bbb, ccc, ddd'
Table #table int @id, varchar @keyword
              1 aaa, bbb, ccc, ddd
              2 aaa, ccc
              3 aaa
              4 aaa, bbb, ccc
Order of matching results after entering keywords
              1 aaa, bbb, ccc, ddd
              4 aaa, bbb, ccc
              2 aaa, ccc
              3 aaa

恶魔右指

select * from table name
order by len (column name) desc--define sorting by number of characters

kxiaper

create table test (keyword varchar (100))
insert test values ​​('aaa, bbb, ccc, ddd')
insert test values ​​('aaa, ccc')
insert test values ​​('aaa')
insert test values ​​('aaa, bbb, ccc')

declare @testdb varchar (500)
set @ testdb = 'aaa, bbb, ccc, ddd'

create table #tmp (a varchar (100))
declare @aaa varchar (8000)
set @ aaa = 'insert #tmp select * from (select' '' + replace (@testdb, ',', '' 'as a union all select' '') + '' ') as a'
  -#tmp is to split @testdb by comma
exec (@aaa)
 
select * from test where exists (select 1 from #tmp where ',' + test.keyword + ',' like '%,' + a + ',%')
order by len (keyword) desc

drop table test, # tmp

--result
/ *
keyword
-----------
aaa, bbb, ccc, ddd
aaa, bbb, ccc
aaa, ccc
aaa
* /

gaoqingfeng

create table a (aname varchar (100))

declare @str varchar (100), @ T_sql varchar (8000)
set @str = 'aaaa, bbbb, cccc, dddd'
set @ T_sql = 'insert a select' ''
set @ T_sql = @ T_sql + replace (@str, ',', '' 'union all select' '') + '' ''
--select @T_sql

exec (@T_sql)

--select * from a
declare @t table (id int identity (1,1), keyword varchar (100))
insert @t select 'aaaa, bbbb, cccc, dddd'
union all select 'aaaa, bbbb'
union all select 'aaaa'
union all select 'aaaa, bbbb, cccc'
union all select 'eeee, ffff'
--select * from @t
--select keyword, a. * from a right join @t b on charindex (',' + a.aname + ',', ',' + b.keyword + ',')> 0

select keyword from a inner join @t b on charindex (',' + a.aname + ',', ',' + b.keyword + ',')> 0 group by keyword order by count (*) desc

drop table a

Yesterday, I worked over half an hour for your question. At that time, I thought about it a bit. I wanted to retrieve the data that does not exist in the query condition association table, such as a, b, c, d; I remembered thinking about it on the road! Hey, half an hour wasted

red8174gf

Thank you for the solutions ofkxiaperandgaoqingfeng. Thegaoqingfengmethod has a higher matching accuracy for multiple keywords.