Keyword Search Help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • blaw1010
    New Member
    • Sep 2006
    • 1

    Keyword Search Help

    I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

    Any input would be greatly appreciated. Thanks

    Here is my stored procedure:


    create procedure procSearch

    @TrainingCertif ication varchar(50) = null

    as
    begin

    select *
    from TrainingCertifi cations
    where TrainingCertifi cationsType LIKE '%' + @TrainingCertif ication + '%'

    end;
  • shreyaas
    New Member
    • Sep 2006
    • 3

    #2
    I am a newbie..but what I guess is:
    try to do this in 2 procedures instead of one. The inner procedure should find out all key words by slicing the userinput with the delimiter like ';' and the outer procedure then takes each of this slice to do the search..

    Comment

    • Meme
      New Member
      • Oct 2006
      • 1

      #3
      you need to use code to break up what they enter into the textbox into different variables, which you then use either - use individually in one repeatedl stored procedure. Or, use in a single stored procedure with many parameters.

      e.g. code something a bit like ...

      string mySearchTerm = txbSearch.Text;
      string myParameter = mySearchTerm.Su bstring(mySearc hTerm.IndexOf(" ;"), mySearchTerm.In dexOf(" "));

      then call your stored procedure.

      Hoep that helps.

      Comment

      • Richard S
        New Member
        • Apr 2007
        • 9

        #4
        Originally posted by Meme
        you need to use code to break up what they enter into the textbox into different variables, which you then use either - use individually in one repeatedl stored procedure. Or, use in a single stored procedure with many parameters.

        e.g. code something a bit like ...

        string mySearchTerm = txbSearch.Text;
        string myParameter = mySearchTerm.Su bstring(mySearc hTerm.IndexOf(" ;"), mySearchTerm.In dexOf(" "));

        then call your stored procedure.

        Hoep that helps.
        altough it aint my thread, id like to thank you alot for this! helped me out alot! ty man

        Comment

        • sayedul
          New Member
          • Oct 2007
          • 12

          #5
          Hi,

          You can make a sql function with a parameter string to return a table with all the semicolon separate words as in different records. Use the function in the stored procedure. Use cursor to get different words from the table returned by the function within the stored procedure. Use the select statement for different words in cursor loop to fill a temporary table. Finally return the temporary table records. This should serve your requirement. You can ask me for the detailed script if you still cannot prepare the script.

          Thanks.

          Sayedul


          Originally posted by blaw1010
          I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

          Any input would be greatly appreciated. Thanks

          Here is my stored procedure:


          create procedure procSearch

          @TrainingCertif ication varchar(50) = null

          as
          begin

          select *
          from TrainingCertifi cations
          where TrainingCertifi cationsType LIKE '%' + @TrainingCertif ication + '%'

          end;

          Comment

          • iburyak
            Recognized Expert Top Contributor
            • Nov 2006
            • 1016

            #6
            If you are interested in database solution try this:

            1. create function on database side.

            [PHP]CREATE FUNCTION Split(@String nvarchar(4000), @Delimiter char(1))
            RETURNS @Results TABLE (ID int, Items nvarchar(4000))
            AS

            BEGIN
            DECLARE @INDEX INT
            DECLARE @SLICE nvarchar(4000)
            DECLARE @ID int

            SELECT @INDEX = 1, @ID = 1
            WHILE @INDEX !=0


            BEGIN
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SELECT @INDEX = CHARINDEX(@Deli miter,@STRING)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
            SELECT @SLICE = LEFT(@STRING,@I NDEX - 1)
            ELSE
            SELECT @SLICE = @STRING
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
            SELECT @ID = @ID + 1
            -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
            SELECT @STRING = RIGHT(@STRING,L EN(@STRING) - @INDEX)
            -- BREAK OUT IF WE ARE DONE
            IF LEN(@STRING) = 0 BREAK
            END
            RETURN
            END
            [/PHP]

            2. Create procedure.

            [PHP]create procedure procSearch

            @TrainingCertif ication varchar(50) = null

            as
            begin

            select *
            from TrainingCertifi cations
            join Split(@Training Certification, ';') on charindex(items , LTRIM(RTRIM(Tra iningCertificat ionsType))) > 0

            end[/PHP]


            I can't test this code but I tested on my database with my tables and it worked.

            Good Luck.

            Comment

            • sayedul
              New Member
              • Oct 2007
              • 12

              #7
              Hi,

              The detail of the DB solution is greate especially the select statement written in the stored procedure.

              Thanks.

              Originally posted by iburyak
              If you are interested in database solution try this:

              1. create function on database side.

              [PHP]CREATE FUNCTION Split(@String nvarchar(4000), @Delimiter char(1))
              RETURNS @Results TABLE (ID int, Items nvarchar(4000))
              AS

              BEGIN
              DECLARE @INDEX INT
              DECLARE @SLICE nvarchar(4000)
              DECLARE @ID int

              SELECT @INDEX = 1, @ID = 1
              WHILE @INDEX !=0


              BEGIN
              -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
              SELECT @INDEX = CHARINDEX(@Deli miter,@STRING)
              -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
              IF @INDEX !=0
              SELECT @SLICE = LEFT(@STRING,@I NDEX - 1)
              ELSE
              SELECT @SLICE = @STRING
              -- PUT THE ITEM INTO THE RESULTS SET
              INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
              SELECT @ID = @ID + 1
              -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
              SELECT @STRING = RIGHT(@STRING,L EN(@STRING) - @INDEX)
              -- BREAK OUT IF WE ARE DONE
              IF LEN(@STRING) = 0 BREAK
              END
              RETURN
              END
              [/PHP]

              2. Create procedure.

              [PHP]create procedure procSearch

              @TrainingCertif ication varchar(50) = null

              as
              begin

              select *
              from TrainingCertifi cations
              join Split(@Training Certification, ';') on charindex(items , LTRIM(RTRIM(Tra iningCertificat ionsType))) > 0

              end[/PHP]


              I can't test this code but I tested on my database with my tables and it worked.

              Good Luck.

              Comment

              • karthickbabu
                New Member
                • Sep 2007
                • 33

                #8
                Originally posted by shreyaas
                I am a newbie..but what I guess is:
                try to do this in 2 procedures instead of one. The inner procedure should find out all key words by slicing the userinput with the delimiter like ';' and the outer procedure then takes each of this slice to do the search..


                Hi

                Create FULL TEXT CATALOG in Sql Server. Search how to create Catalog in online or Sql Server Books online help. Its very easy to find keyword search help. Its ack like small search engine. It creating character based index in DataBase. When you type keyword and search it will shows your required search

                way to create full text catalog:
                1. Expand a server group, and then expand a server.
                2. Expand Databases, and then right-click the database where you want the full- text catalog.
                3. Click New, and then click New Full-Text Catalog.
                4. Complete the New Full-Text Catalog dialog box.

                You must install your Sql Server in Custom Installation. Then only we create Full Text Catalog. We create Full Text Catalog using Sql queries also. search in sql server book online file (chm file). We create schedule for catalog. Its automatically populated while your database update or set the time interval when ever we want the catalog can be populated.

                This is the simple way to create keyword search help

                Comment

                • sayedul
                  New Member
                  • Oct 2007
                  • 12

                  #9
                  Originally posted by blaw1010
                  I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

                  Any input would be greatly appreciated. Thanks

                  Here is my stored procedure:


                  create procedure procSearch

                  @TrainingCertif ication varchar(50) = null

                  as
                  begin

                  select *
                  from TrainingCertifi cations
                  where TrainingCertifi cationsType LIKE '%' + @TrainingCertif ication + '%'

                  end;

                  Hi,

                  I have written another very simple solution of the problem. The solution is tested. The procedure is as follows:

                  Code:
                  create procedure procSearch
                  @TrainingCertification varchar(50) = null
                  as
                  begin
                  select *
                  from TrainingCertifications
                  where (';' + replace(@TrainingCertification, '; ', ';') + ';') 
                  like ('%;' + TrainingCertificationsType + ';%')
                  end

                  - Sayedul Haque

                  Comment

                  Working...