Search for some word - 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

SQL Server Development (2000)

Search for some word

Author

Topic

pharvey
Starting Member

20 Posts

Posted - 2006-03-06 : 10:50:05

I want to do a search in some field and I want to pass a parameter that contain 1 or some word. Is it possible to obtain the results that contains those words but not necessary in the order. I did a 'like' but we return only the row that contains the string we send.

For the moment I have this store procedure but I know this is bad:
CREATE PROCEDURE sp_News_Get
@SearchText varchar(150)
AS
SELECT * FROM tblNews
WHERE Title like '%@SearchText%' OR Description like '%@SearchText%' OR Header like '%@SearchText%' OR Credit like '%@SearchText%'
GO

If I wanna search this string: 'congo president'
I wanna receive all row that contain in their text congo and president:
I have congo on Description and president on Credit it's good.
I have congo on Description and president on Description it's good.
I have congo on Description and I did'nt found president it's bad.

Is it enough clear? Sorry for my bad english.

Thank you

Pascal

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-06 : 11:29:50

Try

CREATE PROCEDURE sp_News_Get_new
@SearchText varchar(150)
AS

declare @s varchar(8000)
Set @s = 'SELECT * FROM tblNews WHERE Title like ''%' 
	+ @SearchText + '%'' OR Description like ''%' 
	+ @SearchText + '%'' OR Header like ''%' 
	+ @SearchText + '%'' OR Credit like ''%'
	+ @SearchText + '%'''

Exec (@s)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-06 : 13:05:26

Srinika - I think it's a bit more complicated than that. In pharvey's example, the search text 'congo president' would not be found by any of those conditions as it is split across multiple fields (i.e. the work 'congo' exists within the Description field and the word 'president' exists within the Credit field for a single row. I think you're going to have to parse the string into a table (one word per row), left join to this multiple times, and then use the where clause to constrain to those rows that you want. Do you only want to match whole works (i.e. would you match 'Presidential' or just 'President'? I'll try and come up with some more detail when I get a minute.

Mark

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-06 : 13:35:31

Oh my bad

,

mwjdavidson, you are correct. Initially I didn't go through it carefully. I saw that the use of variable in the like clause is not proper. I assumed that was the issue.

Sorry pharvey,
My way works only for a single word as the search.

As mwjdavidson suggests,
1. U may need to break the search text to the words.
2. check for each word, whether each field matches (u can make use of the code portion that I have already given)
3. Keep a variable to find whether all the words in search are found

Or wait for mwjdavidson ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources