Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Exclamation Unanswered: Search column by column

    Suppose I have a table table1 with three columns like

    col1 col2 col3
    -------------- ---------------------------- --------------------
    This is a test How are you? I am fine
    Mohunbagan wins B.A from Calcutta SOurav is great

    I have to create a search engine which will search the definite text from these three fields.It will start from col1,if does not get it will go to col2,if does not get there it will go to col3.But if it gets at col1 or col2 it will not go any further.
    Suppose I am looking for 'test'.It will be found in col1.So it will stop executing any further processing.If I look for Calcutta then it will start searching from col1 and will not get it there,so it should come to col2 and get it there.Suppose I like to search for 'a' it is found both in col1 and col2 but it should only return me the reference of col1.
    I wrote a sp like this-
    create proc spsearch
    (@search varchar(5000))
    as
    begin
    if(select count(*) from table1 where col1 like '%'+@search+'%')>=1
    begin
    select col1 from table1 where col1 like '%'+@search+'%'
    return
    end
    if(select count(*) from table1 where col2 like '%'+@search+'%')>=1
    begin
    select col2 from table1 where col2 like '%'+@search+'%'
    return
    end
    if(select count(*) from table1 where col3 like '%'+@search+'%')>=1
    begin
    select col3 from table1 where col3 like '%'+@search+'%'
    return
    end

    When I am executing this query I am not getting expected result every time(some time I am getting).There is no problem col1 but problems are there for col2 and col3.
    Can any body help me please?
    subhasishray@sify.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Would this code work for you:

    create proc spsearch
    (@search varchar(5000))
    as

    begin
    select col1 from table1 where col1 like '%'+@search+'%'
    if @@Rowcount = 0
    begin
    select col2 from table1 where col2 like '%'+@search+'%'
    if @@Rowcount = 0 select col3 from table1 where col3 like '%'+@search+'%'
    end
    end

    Though this is more tidy programmatically:

    create proc spsearch (@search varchar(5000))
    as
    begin
    declare @ResultSet table (ColString varchar(5000))
    insert into @ResultSet(ColString) select col1 from table1 where col1 like '%'+@search+'%'
    if @@Rowcount = 0
    begin
    insert into @ResultSet(ColString) select col2 from table1 where col2 like '%'+@search+'%'
    if @@Rowcount = 0 insert into @ResultSet(ColString) select col3 from table1 where col3 like '%'+@search+'%'
    end
    select * from @Resultset
    end

    blindman

  3. #3
    Join Date
    Oct 2003
    Posts
    83
    Sorrry your way will not help me.
    Anyway I solve my problem.
    I wrote the query this way
    CREATE PROC getNewsdetails2
    (@search VARCHAR(4000),
    @News_type INT
    )
    AS
    BEGIN

    IF (SELECT COUNT(*)FROM tblnews WHERE news_type= @News_type
    AND major_headline like '%'+@search+'%')>=1
    BEGIN
    SELECT news_id,Major_headline FROM tblnews WHERE news_type= @News_type
    AND major_headline like '%'+@search+'%'

    RETURN
    END

    IF (SELECT COUNT(*)FROM tblnews WHERE news_type= @News_type
    AND minor_headline like '%'+@search+'%')>=1
    BEGIN
    SELECT news_id,Major_headline FROM tblnews WHERE news_type= @News_type
    AND minor_headline like '%'+@search+'%'

    RETURN
    END

    IF (SELECT COUNT(*)FROM tblnews WHERE news_type= @News_type
    AND news_text like '%'+@search+'%')>=1
    BEGIN
    SELECT news_id,Major_headline FROM tblnews WHERE news_type= @News_type
    AND news_text like '%'+@search+'%'

    RETURN
    END

    END

    It is givibg me the correct reasult.
    Subhasish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •