Tammu
asked on
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]( @searchwor ds 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:TextB ox>
<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(Configuratio nManager.C onnectionS trings["My siteConn"] .Connectio nString);
mycon.Open();
SqlCommand cmd = new SqlCommand("dbo.fulltextpr ocedure", mycon);
cmd.CommandType = CommandType.StoredProcedur e;
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.
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](
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:TextB
<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(Configuratio
mycon.Open();
SqlCommand cmd = new SqlCommand("dbo.fulltextpr
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add(new SqlParameter("@searchwords
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
mycon.Close();
mycon.Dispose();
}
catch (Exception ex)
{
lblStatus.Text = ex.Message;
}
}
Thanks i appreciate it.
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.
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.
ASKER
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.
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.
Search this forum for "split function", "multiple term search", "dynamic search". There are numerous examples. Like here:
https://www.experts-exchange.com/questions/23068635/Multiple-keyword-searching-syntax-help-please.html
https://www.experts-exchange.com/questions/23068635/Multiple-keyword-searching-syntax-help-please.html
ASKER
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]( @searchwor ds 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
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](
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
ASKER
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
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
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER
Hello Sir,
Its me again, i have started a new post at
http://www.experts-exchang e.com/Prog ramming/La nguages/.N ET/.NET_Fr amework_2. 0/Q_241481 25.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.
Its me again, i have started a new post at
http://www.experts-exchang
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.
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)