Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

help searching multiple words Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 8:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:07 AM
Points: 32, Visits: 307
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.
Post #1444425
Posted Friday, April 19, 2013 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 11,950, Visits: 10,983
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)
Post #1444435
Posted Friday, April 19, 2013 8:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:07 AM
Points: 32, Visits: 307
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

Post #1444458
Posted Friday, April 19, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 11,950, Visits: 10,983
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)
Post #1444463
Posted Friday, April 19, 2013 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:07 AM
Points: 32, Visits: 307
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.

Post #1444466
Posted Friday, April 19, 2013 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 11,950, Visits: 10,983
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)
Post #1444481
Posted Friday, April 19, 2013 9:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:07 AM
Points: 32, Visits: 307
reading time for full text search then )
much obliged Sean.
Post #1444488
Posted Friday, April 19, 2013 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 11,950, Visits: 10,983
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)
Post #1444500
Posted Friday, April 19, 2013 10:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:07 AM
Points: 32, Visits: 307
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 )
Post #1444507
Posted Friday, April 19, 2013 5:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 


Post #1444653
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse