word or words search using Stored Proced

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

General SQL Server Forums

New to SQL Server Programming

word or words search using Stored Proced

Author

Topic

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-02-03 : 02:54:31

I required word or words give it anywhere in the search column using Stored Procedure in SQL Server 2005

Dont using FullText Search method. Bcz FullText Search is very slow.

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 03:18:11

select * from table where columnName like 'word%'

Ved Prakash Jha

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:31:10

use this sample procedure
create proc dbo.usp_sample
(
@type varchar(32),
)
AS
SET NOCOUNT ON
BEGIN

select * from urtable where empname like '%'+@type+'%' -- inbetween word

select * from urtable where empname like ''+@type+'%' -- type will starts with given parameter

select * from urtable where empname like '%'+@type+'' -- end with given word

END
SET NOCOUNT OFF

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-02-03 : 03:37:08

quote:
Originally posted by bklr

use this sample procedure
create proc dbo.usp_sample
(
@type varchar(32),
)
AS
SET NOCOUNT ON
BEGIN

select * from urtable where empname like '%'+@type+'%' -- inbetween word

select * from urtable where empname like ''+@type+'%' -- type will starts with given parameter

select * from urtable where empname like '%'+@type+'' -- end with given word

END
SET NOCOUNT OFF

select blogid,blogcontent from blogmaster
where blogcontent = 'even though the box'
We require full words search or single 'even' or 'though' or 'even though'...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:39:34

change like this
select blogid,blogcontent from blogmaster
where blogcontent like '%even%'
---
blogcontent like '%even though%'

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-02-03 : 03:49:58

quote:
Originally posted by bklr

change like this
select blogid,blogcontent from blogmaster
where blogcontent like '%even%'
---
blogcontent like '%even though%'

Sorry. We require this fields full list are required. that means
where ever this contains words should be listed.
1. checking the full words
2. Then checking 'even'
3. checking 'though'
Totally full records are required

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:53:24

i think this is ur requirement check this once
select blogid,blogcontent from blogmaster
where blogcontent like '%even%' or blogcontent like 'even though the box' or blogcontent like '%though%'

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-02-03 : 03:56:24

quote:
Originally posted by bklr

i think this is ur requirement check this once
select blogid,blogcontent from blogmaster
where blogcontent like '%even%' or blogcontent like 'even though the box' or blogcontent like '%though%'

condition is same as 'Google search' but here only one field

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-03 : 08:45:19

First, bklr, this is redundant:

quote:
Originally posted by bklr

select * from urtable where empname like '%'+@type+'%' -- inbetween word

select * from urtable where empname like ''+@type+'%' -- type will starts with given parameter

select * from urtable where empname like '%'+@type+'' -- end with given word

You do not need to search for all three instances. '%SEARCH%' will find text that starts with the search string, ends with the search string, or contains the search string.

Rev, you are going to need to write a short user-defined function that parses your search string into separate words and returns it as a table. You can then join this UDF table to your search table using the LIKE operator as above.
No, it is not going to be fast. In fact, I doubt that you are going to come up with a home-grown solution that is faster than the full-text search provided by the expert developers at Microsoft. If there were a faster easier way, they wouldn't have created full-text search feature at all, right?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources