Newbie select question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Newbie select question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2006-06-13 : 11:11:53

Hi all,

I’m having trouble with a Store procedure.

I have a view where I want to select 1 value, based on 2 criteria’s, but one is a tricky string, and in need to try whole string or substrings.

Let me explain.

Some one search on the website ex: big blue widget

I want to like

Select page from tblPages where keyword like ‘%big blue widget%’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages where keyword like ‘%big blue %’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages where keyword like ‘%blue widget%’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages where keyword like ‘%big%’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages where keyword like ‘%blue%’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages where keyword like ‘%widget%’

In case it is found, it will return page.
In case it is not found, it will try to find

Select page from tblPages

Thanks,

Roland

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 17:13:30

Where is LIKE '%BIG WIDGET%'?

Try this code!

-- prepare search parameters
declare @t table (i int identity(0, 1), p varchar(50))

insert @t (p)
select 'big' union all
select 'blue' union all
select 'widget'

-- Output data and do the magic
SELECT		z.Param,
		(SELECT COUNT(*) FROM Table1 WHERE Page LIKE '%' + z.Param + '%') Items
FROM		(
			SELECT	DISTINCT t1.p + CASE WHEN t2.p = t1.p THEN '' ELSE ' ' + t2.p END + CASE WHEN t3.p = t1.p OR t3.p = t2.p THEN '' ELSE ' ' + t3.p END Param
			FROM	@t t1, @t t2, @t t3
			WHERE	t1.i <= t2.i and t2.i <= t3.i
		) z
ORDER BY	2 DESC,
		1

Output look something like


Param           Items
---------------------
big             1,413
big blue          824
big blue widget   514
big widget         90
blue                6
blue widget         3
widget              1

Peter Larsson
Helsingborg, Sweden

mike13
Posting Yak Master

219 Posts

Posted - 2006-06-13 : 18:24:12

Hi Peter,

think i didn't even think of that 'big widget' ;-)
One think i forgot the mention.
The param 'big blue widget' is entered in on string into SP.
And i only want one result back based on the priority (like i showed on in first post)

Thanks a lot,
Roland

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 19:13:14

Ok. Why is "big blue" before "blue widget"? And where should "big widget" be in your "prioritization table"?

Are there always three search words? No less, no more? How does the "priorization table" look like when having four search words a, b, c and d?

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-06-13 : 19:44:37

Sounds like a job for the "full-text indexing" features to me. You've paid for it - might as well use it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-13 : 20:07:47

This might be helpful to you:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

mike13
Posting Yak Master

219 Posts

Posted - 2006-06-14 : 10:56:28

Hi,

Priority is just the most the most words and starting with first.
Can be from 1 to like 10

Tnx,

Mike

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-14 : 13:02:02

Check out the link I sent; if you use the MATCH ANY option, you should get pretty much the results you are looking for, sorted by relevancy (most terms that match). Tweak as needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources