T-SQL CONTAINS

  • 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:

  • 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

  • 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

  • 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

  • "WITH EXECUTE AS CALLER"

    isn't that the default option?

  • 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]


    [font="Arial Narrow"](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.[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply