Stored Procedure with parameter for a contains in select

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)

Stored Procedure with parameter for a contains in select

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-23 : 08:55:54

zwirne writes "passing the name to the sp that contains:

SELECT * FROM Company WHERE contains
(Company.name, '"@name*"') order by Company.name

but i dont get any results.

if i try it with a part of a name instead of the param i get
some results.

what am i doning wrong?
thx"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-23 : 09:22:12

get rid of the quotes...

Peace

Rick

sickhead
Starting Member

5 Posts

Posted - 2002-07-30 : 06:05:16

[rick helped me out with the following answer]

SELECT * FROM Company WHERE contains
(Company.name, '"@name*"') order by Company.name

Will not work in SQL because * is only a 1 letter replacement and double quotes
are a definate no-no....

Whereas

SELECT * FROM Company WHERE contains
(Company.name, @name + '%') order by Company.name

Will work fine...

Peace

Rick

[thx to rick once again]

too much of a beautiful thing is wonderfull

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-30 : 07:56:00

Except that '%' has absoloutely no meaning to Full Text Search, * is the prefix term.
Double quotes are extremely important in defining word/phrase boundries (although a complex expression is not required in this case)
e.g. CONTAINS (column, '"text*"') The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and will search for exact matches to text*.

You should parse your input string on the client or server and format it into a properly structured FTS search phrase and then simply use the parameter e.g.

create proc comp_search @name varchar(50)
as

declare @parsed_name varchar(60)
set @parsed_name = '"' + @name + '*"'

SELECT * FROM Company
WHERE contains(Company.name, @parsed_name)
order by Company.name

Also you might want to consider using CONTAINSTABLE to return the rank and also allow you to limit the results returned using the top_n_by_rank parameter.

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources