Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL CONTAINS Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 23, 2008 5:18 AM
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
Post #518849
Posted Wednesday, June 18, 2008 4:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 2:46 PM
Points: 131, Visits: 177
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


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
(India)
Post #518872
Posted Wednesday, June 18, 2008 5:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 23, 2008 5:18 AM
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
Post #518884
Posted Wednesday, June 18, 2008 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 2:46 PM
Points: 131, Visits: 177
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
(India)
Post #519010
Posted Thursday, June 19, 2008 7:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
"WITH EXECUTE AS CALLER"

isn't that the default option?
Post #519824
Posted Thursday, June 19, 2008 8:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:08 PM
Points: 1,186, Visits: 1,976
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.
Post #519895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse