| |

VerySource

 Forgot password?
 Register
Search
View: 685|Reply: 4

(SOLVED) Multiple keyword character matching problem

[Copy link]

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-2-14 11:30:02
| Show all posts |Read mode
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
Reply

Use magic Report

0

Threads

93

Posts

46.00

Credits

Newbie

Rank: 1

Credits
46.00

 China

Post time: 2020-4-12 23:00:01
| Show all posts
select * from table name
order by len (column name) desc--define sorting by number of characters
Reply

Use magic Report

0

Threads

30

Posts

25.00

Credits

Newbie

Rank: 1

Credits
25.00

 China

Post time: 2020-4-12 23:15:01
| Show all posts
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
* /
Reply

Use magic Report

1

Threads

23

Posts

18.00

Credits

Newbie

Rank: 1

Credits
18.00

 China

Post time: 2020-5-1 10:00:01
| Show all posts
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
Reply

Use magic Report

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-5-21 06:15:01
| Show all posts
Thank you for the solutions ofkxiaperandgaoqingfeng. Thegaoqingfengmethod has a higher matching accuracy for multiple keywords.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list