Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help searching multiple words


help searching multiple words

Author
Message
a20213
a20213
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 428
hello, i am trying to make a procedure that returns values if and only the query matches all words .. EX:

declare @Names TABLE
(
name varchar(100)
)

DECLARE @Searchs TABLE
(
word varchar(100)
)

insert into @Searchs (word) select items from dbo.Split('John Andrews', ' ')

insert into @Names values ('John Andrews')
insert into @Names values ('John Adams')
insert into @Names values ('John Matthews')
insert into @Names values ('Mark Snow')

select name,word from @Names
inner join @Searchs on name like '%' + word + '%'



this produces the following result


name word
John Andrews John
John Adams John
John Matthews John
John Andrews Andrews



my goal is trying to return on 1 result - John Andrews.
what do i need to do to achieve this ?

PS: Is this "procedure" advisable to search multiple words ?
Thank you in advance.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
a20213 (4/19/2013)
hello, i am trying to make a procedure that returns values if and only the query matches all words .. EX:

declare @Names TABLE
(
name varchar(100)
)

DECLARE @Searchs TABLE
(
word varchar(100)
)

insert into @Searchs (word) select items from dbo.Split('John Andrews', ' ')

insert into @Names values ('John Andrews')
insert into @Names values ('John Adams')
insert into @Names values ('John Matthews')
insert into @Names values ('Mark Snow')

select name,word from @Names
inner join @Searchs on name like '%' + word + '%'



this produces the following result


name word
John Andrews John
John Adams John
John Matthews John
John Andrews Andrews



my goal is trying to return on 1 result - John Andrews.
what do i need to do to achieve this ?

PS: Is this "procedure" advisable to search multiple words ?
Thank you in advance.


That somewhat depends on what your Split function looks like. Is it using a while loop, xml or a tally table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a20213
a20213
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 428
this is the Split function

FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) returns @Results TABLE (Items nvarchar(4000))
as
begin
declare @index int
declare @slice nvarchar(4000)

select @index = 1
if @String is null return

while @index != 0
begin
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index - 1)
else
select @slice = @String

insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) - @index)
if len(@String) = 0 break
end
return
end


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
a20213 (4/19/2013)
this is the Split function

FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) returns @Results TABLE (Items nvarchar(4000))
as
begin
declare @index int
declare @slice nvarchar(4000)

select @index = 1
if @String is null return

while @index != 0
begin
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index - 1)
else
select @slice = @String

insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) - @index)
if len(@String) = 0 break
end
return
end



Take a look at the link in my signature about splitting strings. It will blow the doors off the while loop splitter for performance.

One challenge I see here is in your example I don't really understand what you are trying to do. You have full names in your @Names table and your have the same full name in @Searchs. Why do you want/need to split them to find the same value? I suspect that your example here is greatly simplified and it does not have quite enough details to help with your real situation.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a20213
a20213
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 428
Yes, the example is very simplified, what i want to do is to find results that match all words of the search.

If i have the text "i have lunched with John Andrews and it was decided to postpone ... bla bla", so when searching in sentences/text, i want to return this sentence, because both words of the search criteria were found here.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
a20213 (4/19/2013)
Yes, the example is very simplified, what i want to do is to find results that match all words of the search.

If i have the text "i have lunched with John Andrews and it was decided to postpone ... bla bla", so when searching in sentences/text, i want to return this sentence, because both words of the search criteria were found here.



OK that is what I thought. You would probably have a lot better luck using full text search instead of trying to parse this do some sort of finagling with it.

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Without doing a full text searching you would need to do a wildcard search on both sides of the stored value. This will be horrible for performance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a20213
a20213
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 428
reading time for full text search then )
much obliged Sean.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16669 Visits: 17030
Even though that is the best approach for this situation you should still look at replacing your split function with the one I pointed you too. Post back if you have any issues and I will try to help as best I can.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a20213
a20213
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 428
Sean Lange (4/19/2013)
Even though that is the best approach for this situation you should still look at replacing your split function with the one I pointed you too. Post back if you have any issues and I will try to help as best I can.


It's has been taken care of )
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
Full-text search per Sean's recommendation definitely sounds like the best long-term solution. But implementing full-text search is not trivial either. To be effective you will need to look up a good parser function for "Google-like" searches (for example) and then learn how to write queries using the proper look-up methods for the full-text index. That will require you make some decisions such as what rowset function to use (freetext vs contains for example).

In your case, since you are looking for exact discrete matches, Sean's other suggestion to use a better split method may be enough by itself to get what you want. Point by point:

1. Split the target string via CROSS APPLY by spaces into a temp table using a good inline table-value splitter function. This will give you a table with every word of the target in its own row.

2. Use DelimitedSplit8K to split your string of search words into another table (the function will do that for you).

3. Then CROSS APPLY the first table with the second to filter your results.

pseudo-code:



SELECT
s.strInput --string being searched
,dsk1.ItemValue --terms in the search term CSV that match SplitOnSpace temp table
FROM
SourceTable s
CROSS APPLY
SplitOnSpace(s.strInput) spl1
CROSS APPLY
DelimitedSplit8K(s.SearchTermCSV,',') dsk1
WHERE
spl1.strValue = dsk1.ItemValue




strInput = the text you are parsing. You would get a temp table with (for example) an output column strValue with each word of the target string now parsed into its own row.

SearchTermCSV = this is a delimited list of search terms. It gets parsed into its own temp table.

Then in the WHERE clause you use a join of the two temp tables to see if there are any matches.

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search