Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: search database for "any terms" stored proc help

    hey all,

    basically, what I am trying to achieve to 2 types of search functions...

    Search for All terms (easy and complete) and search for Any Terms...

    the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.

    this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.

    What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.

    Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...

    or any other hints/tips/tricks would also be helpful.

    Thanks Everyone!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Server version, please?
    And are you searching every column of the table, or only specific columns?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by blindman
    SQL Server version, please?
    And are you searching every column of the table, or only specific columns?
    SQL Server 2005.

    and not every column of table, only a select few: ID, Name, Description

    Cheers

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if you have a parameter passed to the procedure, then you can do something like this:

    create procedure dbo.sp__myproc (@param varchar(8000)) as
    declare @id table ([id] int null)
    declare @name table ([name] varchar(100))
    declare @desc table ([description varchar(8000))
    if isnumeric(@param)=1 insert @id select cast(@param as int)
    insert @name select cast(@param as varchar(100))
    insert @desc select @param
    select tbl.* from dbo.myTable tbl
    inner join @name n on tbl.name = n.name
    union
    select tbl.* from dbo.myTable tbl
    inner join @desc d on tbl.description = d.description

    You will end up hitting your prod table 3 times, but if indexes exist on those fields, - the impact will be sustainable.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a function you can create, compatible with 2005, that will split a delimited string into its components and return it as a table that you can join or query like any other table:
    Code:
    create function ParseString(@String varchar(500), @Delimiter char(1))
    returns table
    as
    
    --function ParseString
    --blindman, 5/20/2008
    --Parses a string based upon a given single-character delimiter,
    --without using loops or a tally table.
    
    --test parameters
    --declare	@String varchar(500)
    --declare	@Delimiter char(1)
    --set	@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
    --set	@Delimiter = '/'
    --set	@String = 'Abracadabra!, Shazam!, Presto!'
    --set	@Delimiter = ','
    --;
    
    return
    (
    with Results as
    	(select	1 as Ordinal,
    		ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
    		convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
    	UNION ALL
    	select	Ordinal+1,
    		ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
    		right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
    	from	Results
    	where	len(Remaining) > 0)
    select	Ordinal,
    	StringValue
    from	Results
    )
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I thought he was passign only 1 parameter value, not a delimited list of values...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I took "Search for all terms" to imply one parameter containing a list (CSV usually) of terms, just because that is one of the most frequently posted questions on the forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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