June 18, 2008 at 3:52 am
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:
June 18, 2008 at 4:52 am
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
June 18, 2008 at 5:17 am
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
June 18, 2008 at 7:37 am
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
June 19, 2008 at 7:07 am
"WITH EXECUTE AS CALLER"
isn't that the default option?
June 19, 2008 at 8:19 am
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,
[font="Courier New"]SELECT
desired columns
FROM
desired table(s)
WHERE CONTAINS( query here )
AND CONTAINS( query here )[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply