Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL CONTAINS


T-SQL CONTAINS

Author
Message
umaramiya-724626
umaramiya-724626
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 33
I have created a function which has varbinary and keyword as varchar(100) as parameters.

I need to find whether the keyword (@Keyword) is in the varbinary (@Resume) variable and return the flag (@Exists) accordingly.

@Resume - varbinary
@Text - varchar(100)
@Exists bit return

If CONTAINS(@Resume,@text) set @Exists = 1

the above shows syntax error. How to achieve the above results.
Help me please Crying
shrikant-279400
shrikant-279400
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 238
umaramiya (6/18/2008)
I have created a function which has varbinary and keyword as varchar(100) as parameters.

I need to find whether the keyword (@Keyword) is in the varbinary (@Resume) variable and return the flag (@Exists) accordingly.

@Resume - varbinary
@Text - varchar(100)
@Exists bit return

If CONTAINS(@Resume,@text) set @Exists = 1

the above shows syntax error. How to achieve the above results.
Help me please Crying


I would fail since CONTAINS must have column name or column list as first argument. Can you please detail your exact requirement? I don't think its good idea if you are doing it in scalar function. A table valued function should be a good choice here.

Regards
Shrikant Kulkarni
umaramiya-724626
umaramiya-724626
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 33
I am working on a Job Portal
This is the scenario.

I have word Document Saved (resume of the Candidate) in one Table and another Title (Resume Header) column thats saved in another Table. Both are enabled for Full Text.

When keywords are supplied I have to check in both the Tables and return result with other criteria.

I want to do as a function as I need to format the keywords supplied before I start matching.

(e.g) Java and Dot Net --- Should search for both Java and Dot Net in the Resume or Resume Header

Java or Dot Net --- Should search either Java or Dot Net in the Resume or Resume Header

"Java" --- Should search exact match for Java in the Resume or Resume Header

"*Java*" --- Should search wild card match for Java in the Resume or Resume Header.
along with this I have other search filters as well.
Thast why wanted to do a function which return bit and invoke it in my query
shrikant-279400
shrikant-279400
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 238
I don't have much time right now to think on more optimize solution on your problem, but this code should help you out.
CREATE FUNCTION dbo.match (@resume varchar(100),@keyword varchar(10))
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @flag bit
Select @flag = case when exists(select 1
from resume_header rh join resume r
on rh.rh_id=r.rid
where contains(resume_title,@keyword) and
contains(resume,@keyword))

then 1
end
return @flag
end


here you run it
declare @yesno bit 
select @yesno=dbo.match ('java','jaaava')
select isnull(@yesno ,'FALSE') as found



Regards
Shrikant Kulkarni
Seggerman-675349
Seggerman-675349
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 559
"WITH EXECUTE AS CALLER"

isn't that the default option?
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
I don't see why the data is split between the two tables. It would be easier if they were both in the same table. You would have only one full-text catalog and you can specify the two columns in the same CONTAINS query.

That said, continue to use the CONTAINS function. Just use a double WHERE clause. For example,

SELECT
desired columns
FROM
desired table(s)
WHERE CONTAINS( query here )
AND CONTAINS( query here )



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search