April 19, 2013 at 8:08 am
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 AndrewsJohn
John Adams John
John MatthewsJohn
John AndrewsAndrews
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.
April 19, 2013 at 8:19 am
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 AndrewsJohn
John Adams John
John MatthewsJohn
John AndrewsAndrews
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2013 at 8:35 am
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
April 19, 2013 at 8:42 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2013 at 8:50 am
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.
April 19, 2013 at 9:24 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2013 at 9:36 am
reading time for full text search then )
much obliged Sean.
April 19, 2013 at 10:05 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2013 at 10:26 am
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 )
April 19, 2013 at 5:41 pm
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.
April 21, 2013 at 2:41 pm
Cool, thank you. Meanwhile i was trying to solve using my old attempt, got it also ..finally )
declare @Phrases TABLE
(
phrase varchar(100)
)
DECLARE @Searchs TABLE
(
word varchar(100)
)
declare @NumberWords tinyint
insert into @Searchs (word) select item from dbo.DelimitedSplit8K('they angry', ' ')
Select @NumberWords = COUNT(*) from @Searchs
insert into @Phrases values ('First time you''ve seen a giant, Jon Snow?')
insert into @Phrases values ('Well, don''t stare too long. They''re shy.')
insert into @Phrases values ('When they stop being shy, they get angry.')
insert into @Phrases values ('And when they''re angry,')
insert into @Phrases values ('I''ve seen them pound a man straight into the ground')
insert into @Phrases values ('like a hammer on a nail.')
select * from (select phrase, @NumberWords as NW,COUNT(phrase) as NP from @Phrases
inner join @Searchs on phrase like '%' + word + '%'
group by phrase) as Tmp
where Tmp.NW=Tmp.NP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply