June 25, 2008 at 4:02 pm
Hi all
Is there a more elegant way of doing this? I’m searching a book table on title and being passed 10 words. So what I was thing about using is something like
Select from book where
Title like ‘%’ + searchstring1 + ‘%’
And
Title like ‘%’ + searchstring2 + ‘%’
And
.
.
.
Title like ‘%’ + searchstring10 + ‘%’
THEN
Same, but up to searchstring9, then searchstring8, . . . until just searchstring1. Then same stuff with ‘or’s
As you can see this will work, but wondering if there is a better way
Thanks
Bill
June 25, 2008 at 5:45 pm
How are the words being passed and do you have a wad of example book titles we can test with? See the link in my signature for the best way to do that...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 6:07 pm
Hi Jeff
Nice to hear from you again. Hope you are taking the time to enjoy summer some.
Looking at the idea at 30,000 feet is to search a book table and report the most relevant books based on up to 10 strings passed. So, what I’m doing is building a temp table that contains the bookID, and hits. Hits is a relevance factor that I’m assigning depending on the select. A 10 if it is found in all 10 and’s and 10 if found in all or’s by looking at title, author, and category. Once I have this, I populate another temp table that is summed by hits and grouped by bookID. Then select that table by the hits and report back the top 10 hits. I know it’s far from what Google has. . . . .but
I’m going to paste in the complete SP as I have so far. It’s long, but real simple in nature.
SP listing - book data is attached as a zip csv
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SimilarBooks]
(
@SearchString0 varchar (200) = null,
@SearchString1 varchar (200) = null,
@SearchString2 varchar (200) = null,
@SearchString3 varchar (200) = null,
@SearchString4 varchar (200) = null,
@SearchString5 varchar (200) = null,
@SearchString6 varchar (200) = null,
@SearchString7 varchar (200) = null,
@SearchString8 varchar (200) = null,
@SearchString9 varchar (200) = null
)
AS
delete SimilarSearchTemp
delete SimilarSearchTemp2
---------------------------------------- Do Title search ---------------------------------------------------------
-- get all 10 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
10
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
and
title like '% ' + @SearchString5
and
title like '% ' + @SearchString6
and
title like '% ' + @SearchString7
and
title like '% ' + @SearchString8
and
title like '% ' + @SearchString9
-- get all 9 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
9
from
dbo.BookCat
where
Title LIKE '% ' + '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
and
title like '% ' + @SearchString5
and
title like '% ' + @SearchString6
and
title like '% ' + @SearchString7
and
title like '% ' + @SearchString8
-- get all 8 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
8
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
and
title like '% ' + @SearchString5
and
title like '% ' + @SearchString6
and
title like '% ' + @SearchString7
-- get all 7 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
7
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
and
title like '% ' + @SearchString5
and
title like '% ' + @SearchString6
-- get all 6 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
6
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
and
title like '% ' + @SearchString5
-- get all 5 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
5
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
and
title like '% ' + @SearchString4
-- get all 4 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
4
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
and
title like '% ' + @SearchString3
-- get all 3 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
and
title like '% ' + @SearchString2
-- get all 2 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
2
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
and
title like '% ' + @SearchString1
-- get all 1 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
--------------------------------- OR ------------------------------------------
-- get all 10 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
10
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
or
title like '% ' + @SearchString5
or
title like '% ' + @SearchString6
or
title like '% ' + @SearchString7
or
title like '% ' + @SearchString8
or
title like '% ' + @SearchString9
-- get all 9 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
9
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
or
title like '% ' + @SearchString5
or
title like '% ' + @SearchString6
or
title like '% ' + @SearchString7
or
title like '% ' + @SearchString8
-- get all 8 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
8
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
or
title like '% ' + @SearchString5
or
title like '% ' + @SearchString6
or
title like '% ' + @SearchString7
-- get all 7 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
7
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
or
title like '% ' + @SearchString5
or
title like '% ' + @SearchString6
-- get all 6 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
6
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
or
title like '% ' + @SearchString5
-- get all 5 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
5
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
or
title like '% ' + @SearchString4
-- get all 4 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
4
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
or
title like '% ' + @SearchString3
-- get all 3 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
or
title like '% ' + @SearchString2
-- get all 2 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
2
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
or
title like '% ' + @SearchString1
-- get all 1 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
1
from
dbo.BookCat
where
Title LIKE '% ' + @SearchString0
---------------------------------------- Do Author search ---------------------------------------------------------
-- get all 10 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
10
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
and
Author LIKE '% ' + @SearchString5
and
Author LIKE '% ' + @SearchString6
and
Author LIKE '% ' + @SearchString7
and
Author LIKE '% ' + @SearchString8
and
Author LIKE '% ' + @SearchString9
-- get all 9 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
9
from
dbo.BookCat
where
Author LIKE '% ' + '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
and
Author LIKE '% ' + @SearchString5
and
Author LIKE '% ' + @SearchString6
and
Author LIKE '% ' + @SearchString7
and
Author LIKE '% ' + @SearchString8
-- get all 8 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
8
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
and
Author LIKE '% ' + @SearchString5
and
Author LIKE '% ' + @SearchString6
and
Author LIKE '% ' + @SearchString7
-- get all 7 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
7
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
and
Author LIKE '% ' + @SearchString5
and
Author LIKE '% ' + @SearchString6
-- get all 6 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
6
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
and
Author LIKE '% ' + @SearchString5
-- get all 5 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
5
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
and
Author LIKE '% ' + @SearchString4
-- get all 4 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
4
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
and
Author LIKE '% ' + @SearchString3
-- get all 3 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
and
Author LIKE '% ' + @SearchString2
-- get all 2 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
2
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
and
Author LIKE '% ' + @SearchString1
-- get all 1 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
--------------------------------- OR ------------------------------------------
-- get all 10 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
10
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
or
Author LIKE '% ' + @SearchString5
or
Author LIKE '% ' + @SearchString6
or
Author LIKE '% ' + @SearchString7
or
Author LIKE '% ' + @SearchString8
or
Author LIKE '% ' + @SearchString9
-- get all 9 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
9
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
or
Author LIKE '% ' + @SearchString5
or
Author LIKE '% ' + @SearchString6
or
Author LIKE '% ' + @SearchString7
or
Author LIKE '% ' + @SearchString8
-- get all 8 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
8
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
or
Author LIKE '% ' + @SearchString5
or
Author LIKE '% ' + @SearchString6
or
Author LIKE '% ' + @SearchString7
-- get all 7 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
7
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
or
Author LIKE '% ' + @SearchString5
or
Author LIKE '% ' + @SearchString6
-- get all 6 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
6
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
or
Author LIKE '% ' + @SearchString5
-- get all 5 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
5
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
or
Author LIKE '% ' + @SearchString4
-- get all 4 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
4
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
or
Author LIKE '% ' + @SearchString3
-- get all 3 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
3
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
or
Author LIKE '% ' + @SearchString2
-- get all 2 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
2
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
or
Author LIKE '% ' + @SearchString1
-- get all 1 (and gate them babys) in title
insert into dbo.SimilarSearchTemp (BookID, Title, Hits)
Select
BookID,
Title,
1
from
dbo.BookCat
where
Author LIKE '% ' + @SearchString0
Insert into
SimilarSearchTemp2
(
BookID,
Title,
Hits
)
Select
BookID,
Title,
sum(Hits) as Hits
from SimilarSearchTemp
group by BookID, Title
order by BookID
June 25, 2008 at 6:32 pm
Good to see you too... and thanks for the book data in a format that I can actually load easily 😀
Do you have an example set of words that you'd like to lookup with a known answer for testing purposes?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 6:40 pm
Well Jeff, I hope this helps. The SP is going to be called by an ASP program and this is the note that I sent to the programmer.
For similar books for A given book, we would have the title, author, and category. Would it be possible for Anand to pass in up to 10 words. For example, given the book
Title: The Last Juror
By: John Grisham
Category: Audio Books sales
I’d propose that Anand pass
Last, juror, John, Grisham, Audio, Books, Sales
And any word less than or equal to two characters be ignored, and the three character words or ‘and’ & ‘the’ be not passed in.
So, I'm not sure how much this will help, in a way is fuzzy logic
Bill
June 25, 2008 at 8:38 pm
Aw crud... Bill, some of the rows have some commas in the actual data which pretty much screws up "easy". Could you recreate the zip file using Tab delimited instead of comma delimited? Thanks...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 9:19 pm
Hi Jeff
Give this a try
Bill
June 25, 2008 at 9:52 pm
Heh... not good. It looks like you replaced all of the commas with tabs... DTS is trying to turn it into 11 columns because of things like the second row of data where the author name is in the form of lastname, firstname.
If this is in a table, just use DTS to export it as a tab delimited text file.
Otherwise, you need to tell me how you're genning this file so I can help... I really hope that you're not just editing a file that someone gave you...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 10:02 pm
Hi Jeff
I didn't think about using DTS, how dumb of me. So, I didn't see an easy way to just give some of the columns, so it's the whole table.
Thanks
Bill
June 25, 2008 at 10:23 pm
That worked much better... but I gotta get some shuteye... I'll take a whack at it after I get home from work tomorrow...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2008 at 10:28 pm
Thanks Jeff
Sleep well
Bill
June 26, 2008 at 3:56 pm
Hi wdillon and Jef,
I purpose you one solution witch will not replicate the procedure entirely (the strings have same relevance)
First create a table which contains all the strings - you can do that also by passing a single parameter to the procedure and using a string split function
declare @SearchStrings table (SearcString varchar(200))
insert into @SearchStrings values (@SearchString0)
insert into @SearchStrings values (@SearchString1)
......................................
insert into @SearchStrings values (@SearchString9)
and insert into SimilarSearchTemp2 directly hints value
insert into SimilarSearchTemp2(BookID, Title,Hits)
select BookID,Title, sum(Hits) from
(
select BookID,Title,count(*) as Hits from dbo.BookCat
inner join @SearchStrings ss on title like '% ' + ss.SearchString+ '%'
union all
select BookID,Title,count(*) as Hits from dbo.BookCat
inner join @SearchStrings ss on Author like '% ' + ss.SearchString+ '%'
) tmp
I hope will help you.
I think a better approach will use full text search
June 26, 2008 at 7:27 pm
Thanks Jeff
I like it and will give it a try and also look into the full text search feature.
Bill
June 27, 2008 at 7:54 am
Heh... wasn't me, Bill... I'm still trying stuff out...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply