• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

How to split a search string a user enters in stored procedure

Hi All,
i have a Stored Procedure on a table which has full text search enabled on it. what i am doing is i am binding the SP to a gridview to show the results for a search. the search works fine if the Words are consecutive. what i mean is say if i am searching for  Black Shoes , its giving me the results only if the 2 words BLACK SHOES are followed by each other, say in my table if i have Black Velvet Shoes it is not showing up in the result because there is velvet between Black and Shoes. i want even black velvet shoes to show up in the search result if i search for Black Shoes. can someone help me out how to do this, i am stuck. here is complete code

MY QUESTION how can we split the search string which is entered by user and then use that split up words in the stored procedure to perform the search.

NOTE: THE SEARCH STRING CAN BE MORE THEN 2 WORDS ALSO


first the SP:

USE [MySiteDB]
GO
/****** Object:  StoredProcedure [dbo].[fulltextprocedure]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        me
-- Create date: 02/10/2009
-- Description:    search procedure
-- =============================================
CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords AS nvarchar(255))
AS
    DECLARE @searchText NVARCHAR(255);
    SET @searchText = '"' + @searchwords + '"';
   -- Insert statements for procedure here
    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
    FROM dbo.SearchTB
    WHERE CONTAINS(*, @searchText)
                 OR CONTAINS(*, ' FORMSOF (THESAURUS, @searchText) ')
                 OR CONTAINS(*, 'NEAR @searchText NEAR')
return
 now the aspx page :
 
<div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="ItemNumber" HeaderText="ItemNumber" SortExpression="ItemNumber" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            </Columns>
        </asp:GridView>
    </div>
    <span><asp:Label ID="lblStatus" runat="server" /> </span>
 
now code behind  
 
protected void Button1_Click(object sender, EventArgs e)
    {
       
        try
           {

            string srch = "\"" + TextBox1.Text + "\"";
            SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["MysiteConn"].ConnectionString);
            mycon.Open();
 
            SqlCommand cmd = new SqlCommand("dbo.fulltextprocedure", mycon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@searchwords", srch));

            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();

             mycon.Close();
             mycon.Dispose();
             
          }
          catch (Exception ex)
          {
             lblStatus.Text = ex.Message;
          }

         }

Thanks i appreciate it.
0
niceoneishere
Asked:
niceoneishere
  • 7
  • 6
1 Solution
 
dqmqCommented:
That will make your SP quite a bit more involved.  Rather than coding it for you, I offer a conceptual solution which you can attempt, then get back with specific problems or questions.

In your SP, DECLARE @SQL VARCHAR(MAX) to hold a big SQL statement and initialize it to your the first part of your SELECT statement up to and including the word "WHERE ".  Concatenate to that " 1=1".   Now your varchar looks like this:  

"SELECT blah, blah, blah FROM dbo.SearchTB WHERE 1=1"

Code a loop that uses CHARINDEX to step through the parmlist isolating one search term at a time.  For each term found, concatenate " AND (", your 3 CONTAINS operators using the islolated search term, and a close paren to the where clause.  The idea is to dynamically build the SQL statement such that the WHERE clause gets longer and longer with each term found.  

Then execute the SQL

EXEC (@SQL)


0
 
kaylanreilorCommented:
So CONTAINS with NEAR works if "black" and "shoes" are following each others...
I think there is no way to parameterize this behaviour.
Nevertheless, what about a simple query which would return the text when it necessary contains "black" and "shoes". It could be a first filter performed by something like this :
SELECT * FROM mytable
WHERE CONTAINS (mycolumn, 'ISBOUT (blck WEIGHT (1.0), shoes WEIGHT (1.0))'
Or you could also replace CONTAINS by CONTAINSTABLE.
Thus, I'm affraid that your application would have to finish the job by filtering again the result since a text like "My black dog is eating my father's shoes" would be part of the resultset.
0
 
niceoneishereAuthor Commented:
Hi dgmg,
thanks for your reply, is there anyway you can suggest a reference or an example for you have posted, so i can learn from that. sorry for asking like this. i appreciate it.
Hi Kaylanreilor,
i think you misuderstood my post, i never used the NEAR with CONTAINS. without using it, if  just simply do WHERE CONTAINS(*, @searchText)  its giving results with the 2 words following each ie for instance BLACK SHOES, if i type BLACK VELVET SHOES does not return any results.
Thanks all of you, i really appreciate your help in this regard.

 
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
dqmqCommented:
Search this forum for "split function", "multiple term search", "dynamic search".  There are numerous examples.  Like here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23068635.html
0
 
niceoneishereAuthor Commented:
ok sir,
can you please tell if  i am headed in the rigth direction or not, this what i have for so far
CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords As nvarchar(255))
AS
BEGIN
    DECLARE @searchText NVARCHAR(255);
    SET @searchText = '"' + @searchwords + '"';
    DECLARE @searchSQL AS varchar(MAX)
    SET @searchSQL ='
                    SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
                    FROM dbo.SearchTB
                    WHERE CONTAINS(*, ''*' + @searchText + '*'')'
 EXEC(@searchSQL)
END
when i ran excuted the stored procedure with search word black shoes, i got this error
Syntax error near 'black boots' in the full-text search condition '*"black boots"*'.
i know the above SP i wrote does not include spliting up the searchword, i am still trying to learn some of the code i am seeing, but just wondering why the above code wont work sir.
thanks once again
 
0
 
dqmqCommented:
Likely not perfect, but more like this:
CREATE PROCEDURE [dbo].[fulltextprocedure](@searchwords As nvarchar(255))
AS
BEGIN
   DECLARE @searchText NVARCHAR(255)
   DECLARE @searchSQL VARCHAR(MAX)
   DECLARE @pos int
   SET @searchSQL ='SELECT dbo.SearchTB.ItemNumber, dbo.SearchTB.Title, dbo.SearchTB.Price, dbo.SearchTB.Name
                   FROM dbo.SearchTB
                   WHERE 1=1 '
   SET @SEARCHWORDS = @SEARCHWORDS + ' '   --add trailing delimiter
   SET @POS = CHARINDEX(' ',@searchwords)  --locate delimiter
   WHILE @POS <> 0                         --iterate over search terms
      BEGIN
      SET @searchText = LEFT(@searchwords,@pos-1)      --parse next term
      SET @searchwords = STUFF(@searchwords,1,@pos,'') --remove parsed term  
      SET @POS = CHARINDEX(' ',@searchwords)  --locate next delimiter             
      IF @Searchtext = '' CONTINUE 
      SET @SearchSQL=@SearchSQL + ' AND CONTAINS(*,''*' + @searchtext + '*'')'
      END
 
   SELECT @searchSQL  -- for debugging only 
   EXEC(@searchSQL)
END

Open in new window

0
 
niceoneishereAuthor Commented:
Hello Sir,
 You Rock. Thanks a lot for your code, works good. i really appreciate it. means a lot to me. but now i have a question(s).
if i want to use ORDER BY itemnumber would i mention it near WHERE 1=1 or would i mention it near SET @SearchSQL=@SearchSQL + ' AND CONTAINS(*,''*' + @searchtext + '*'')'
if i also want to use conditions like CONTAINS(*, ' FORMSOF (THESAURUS, @searchText) ') how and where will i have to specify it in the code sir.
Thanks i  really appreciate it a million times.

 
0
 
dqmqCommented:
ORDER BY has to go last.  So, between the WHILE block and when you execute the SQL, concatenate the ORDER BY:
  WHILE
     BEGIN
     ...
     END
  SET @SearchSQL = @SearchSQL + ' ORDER BY ItemNumber'

To add other conditions that apply to every search word, you need to make the change inside the WHILE BLOCK.  For example, this should be pretty close:
      ...
      IF @Searchtext = '' CONTINUE
      SET @SearchSQL=@SearchSQL + ' AND ('
      SET @SearchSQL=@SearchSQL + ' CONTAINS(*,''*' + @searchtext + '*'')'
      SET @SearchSQL=@SearchSQL + ' OR CONTAINS (*, ''FORMSOF (THESAURUS,'   + @searchText + ') '')'
      SET @SearchSQL=@SearchSQL + ')'
      END
 

 
0
 
niceoneishereAuthor Commented:
Hello Sir,
Bravo once again you are an angel, saving me all the time. it worked again like a charm, i do have to ask a question , say for example in my database i have both white shoes and offwhite shoes ( like synonyms of white like milky  or snowwhite) etc, and if i search for white shoes is it possible that the synonyms of white also shows up in the result.
so if i search white shoes, the results should include white shoes, snowwhite shoes, offwhite shoes etc.
sorry to keep bugginh you, i hoping i am not testing your patience, but you really rock in helping newbies like me sir.
thanks a lot i appreciate it
 
0
 
dqmqCommented:
I'm not that much an expert on Full Text Search, but isn't that what FORMSOF THESARUS is supposed to do?   Of course, it's your responsibility to populate the THESARUS first.
0
 
niceoneishereAuthor Commented:
Hello Sir,
ok i will try to populate the THESARUS, hopefully i can find some samples ot tutorials on it. i really thank you for everything but one last question sir, how would i search for soemthing like Men's shoes or Boy's shoes . what i mean anything which has apostrophe. again i really thank you for your patience.
 
0
 
dqmqCommented:
Here is a link to Thesarus help:

http://msdn.microsoft.com/en-us/library/ms142491(SQL.90).aspx

If you want to disregard apostrophes, I'd first experiment with varations of the CONTAINS "fuzzy-search" syntax to see if you can get that to work.  Beyond that, you can use the REPLACE function to strip the apsotrophies out of the search words or out of the text being searched.

A word of caution.  I detect some scope creep here.  Understand that searches can get enormously complex and you may be facing an endless series of "refinements" to accomodate approximate matches.  
0
 
niceoneishereAuthor Commented:
Hello Sir,
Thank you for all your help. i wish i could give you more then 500 point because you deserve it. you have been wonderful to a newcomer like me.
i am gonna do some research with both Thesarus and how to build a thesarus xml file and i have gone thru the link you have provided. i will also do some research on "fuzzy-search" i wish microsoft provided some tutorials for people like me.
any way, thats where i am right now. Thanks once again. if you know any references or tutorials please provide a link for them sir. i really appreciate it.
and again You Rock.
 
0
 
niceoneishereAuthor Commented:
Hello Sir,
Its me again, i have started a new post at
http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_2.0/Q_24148125.html
is there anyway you can advice me on how to do what i am trying to sir. i have posted my code there.
I really appreciate it.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now