Seach查询的存储过程 [英] Stored Procedure For Seach query

问题描述

我的网站上有一个搜索面板。当用户点击搜索按钮时，我会检查我的标题或内容中是否有搜索关键字..当用户给出一个单词时，搜索工作正常，但是当用户提供多于一个单词时，它会给出结果，但不是我预期的...我想要按空格分割关键字字符串并检查标题或内容中的每个单词？如何根据我的要求编写StoredProcedures？



注意：如果用户搜索足球意味着结果是gud ..但是当用户给出两个单词如Sony Music player时像关键字匹配标题或内容的确切单词..所以它会给出很少的结果..我想将索尼音乐播放器关键字拆分为索尼，音乐，播放器，并检查每个关键字的标题或内容？

i have a search panel in my website. when user hit search button i will check thier search keywords are available in my title or content.. when user give a single word the search is working fine but when user give more then one words it will give results but not am expected.. I want to split the keyword string by space and check every words in title or content? How to write the StoredProcedures for my request?

note: if user search football means result is gud.. but when user give two words like "Sony Music player" means like keyword match the exact words with title or content.. So it will give very few results.. i want to split the "Sony Music player" keywords as Sony, Music, player and check every one the keywords with title or content?

推荐答案

为了好玩而做的例子：

Example made just for fun:

--temporary table
DECLARE @tmp TABLE(aField NVARCHAR(500))
--insert sample data
INSERT INTO @tmp (aField)
VALUES('Sony Bla bla bla'), ('Son Of The Music'), ('Paranoya Player')
--input text
DECLARE @inputText NVARCHAR(300) = 'Sony Music Player'
--split input text into words ;)
;WITH InputParts AS
(
    SELECT LEFT(@inputText, CHARINDEX(' ', @inputText) -1) AS Word, RIGHT(@inputText, LEN(@inputText) - CHARINDEX(' ', @inputText)) AS Remainder
    WHERE CHARINDEX(' ', @inputText)>0
    UNION ALL
    SELECT LEFT(Remainder, CHARINDEX(' ', Remainder) -1) AS Word, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ', Remainder)) AS Remainder
    FROM InputParts
    WHERE CHARINDEX(' ', Remainder)>0
    UNION ALL
    SELECT Remainder AS Word, NULL AS Remainder
    FROM InputParts
    WHERE CHARINDEX(' ', Remainder)=0
)
SELECT t1.aField, @inputText AS SearchedText, t2.Word AS MatchedWord  
FROM @tmp AS t1 INNER JOIN InputParts AS t2 ON LOWER(t1.aField) LIKE CONCAT('%', LOWER(t2.Word),'%')

结果：

Result:

aField	            SearchedText        MatchedWord
Sony Bla bla bla	Sony Music Player	Sony
Son Of The Music	Sony Music Player	Music
Paranoya Player	    Sony Music Player	Player

基本上，你是什么需要做的是将您的字符串转换为单独的单词sony，music和player，然后发出一个将它们组合在一起的查询：

Basically, what you need to do is convert your string into the separate words "sony", "music", and "player", and then issue a query that assembles them together:

...WHERE MyColumn LIKE '%sony%' OR MyColumn LIKE '%music%' OR MyColumn LIKE '%player%'

或

Or

...WHERE MyColumn LIKE '%sony%' AND MyColumn LIKE '%music%' AND MyColumn LIKE '%player%'

没有自动方法可以做到这一点，但是它并不复杂 - 只是相当乏味！

这里有代码：将列中逗号分隔的数据转换为行以供选择 [ ^ ]将逗号分隔的字符串sony，music，player分隔为单独的单词 - 它将是改变它来改变使用空格的微不足道。然后，您可以将SQL查询组装为字符串并执行它以生成结果列表，而不是创建临时表。

There is no automatic way to do that, but it's not complex - just rather tedious!
There is code here: Converting comma separated data in a column to rows for selection[^] that separates a commad delimited string "sony,music,player" to separate words - it would be trivial to change that to use spaces instead. Then, instead of creating a temporary table, you could assemble an SQL query as a string and EXEC it to produce your results list.

首先，我将获得拆分函数和使用的查询，如这...



从ProductsTB内部加入dbo.Split（@keyword，''）中选择*为标记（描述为'％'+ tokens.Items + '％'或标题如'％'+ tokens.Items +'％'）

first of all i will get split function and used query like this...

Select * from ProductsTB inner join dbo.Split(@keyword, ' ') as tokens on (Description like '%'+ tokens.Items +'%' or Title like '%' + tokens.Items + '%')

这篇关于Seach查询的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！