How to search a word inside a string where word is in a SQL Server table

Question

I have a SQL Server table with two columns Keyword and Emotion.

I want to search a word (which is specified in column Keyword) inside a string which contains a sentence.. for eg:- How are you

I want to execute two queries:

I want to search if the string contains "how" (how is specified in Keyword column)..
If the string contains how, I want to display the corresponding value stored in the Emotion column of the table

This is my sample code (which can be completely wrong :p):-

public partial class message : System.Web.UI.Page
{
    string constring = ConfigurationManager.ConnectionStrings["AnonymiousSocialNetworkConnectionString"].ToString();

    protected void btnsend_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection con = new SqlConnection(constring);
        string value = txtmsg.Text;
        con.Open();

        SqlDataReader myReader = null;
        SqlCommand myCommand = new SqlCommand("select emotion from messageanalysis where keyword CONTAINS'",con);
        myReader = myCommand.ExecuteReader();
        while(myReader.Read())
        {
            lblStatus.Text = myReader["emotion"].ToString();// **this label should Display corresponding value stored in Emotion(column)**//
        }
    }
}

I dont see why you need two queries, the one you have should be sufficient. — BugFinder, Mar 06 '16 at 14:16
I meant to say I want two operations to be performed which is searching of a word & getting the corresponding value — Saif Inamdar, Mar 06 '16 at 14:21
Well, thats what that semi sql does.... So.. Im not getting your question or problem. — BugFinder, Mar 06 '16 at 14:21
the code i provided doesnt work it gives sql exceptions.. So i want to fix this code — Saif Inamdar, Mar 06 '16 at 14:24
$exception {"Incorrect syntax near the keyword 'CONTAINS'."} System.Exception {System.Data.SqlClient.SqlException} — Saif Inamdar, Mar 06 '16 at 14:26
Well, you dont add the closing quote which wont help. Keyword is possibly reserved... — BugFinder, Mar 06 '16 at 14:27
Ps, of you looked at the string you submitted part of that would have been obvious — BugFinder, Mar 06 '16 at 14:28
Does my code syntax and logic seems Right to you? @BugFinder — Saif Inamdar, Mar 06 '16 at 14:28
Logic is fine. Syntax of sql may suffer of keyword is reserved, but if you follow that through.. Hence my confusion, the principal was ok. You didnt post what was wrong. We arent mind readers — BugFinder, Mar 06 '16 at 14:29
Can you fix that errors? m nt getting you :( Am i supposed to use value.contains() method? — Saif Inamdar, Mar 06 '16 at 14:30
i did but it doesnt mention how can i use a local variable in c# like "value" in a sql Statement — Saif Inamdar, Mar 06 '16 at 14:37
The same way you would insert it into any other string in c# — BugFinder, Mar 06 '16 at 14:37

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

You just need only one SQL command and that should be like below -

string searchParam = "hello";                
SqlCommand    myCommand = new SqlCommand("select emotion from messageanalysis where CONTAINS(keyword,@SearchParam)",con);
myCommand.Parameters.Add("@SearchParam", System.Data.SqlDbType.VarChar, 20).Value = searchParam,;

CONTAINS may not work if full-text feature is not enabled on server. Please check this link for more details - Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

If you do not want to enable this feature then use LIKE -

SqlCommand    myCommand = new SqlCommand("select emotion from messageanalysis where keyword like @SearchParam)",con);
myCommand.Parameters.Add("@SearchParam", System.Data.SqlDbType.VarChar, 20).Value = "%" + searchParam + "%";

edited May 23 '17 at 12:23

Community

1
1

answered Mar 06 '16 at 14:28

Vinit

2,540
1
15
22

i dont want to mention 'how' next to keyword as you have shown.. just a direct link to the column which contains hundreds of words one of which can be 'how' – Saif Inamdar Mar 06 '16 at 14:34
it is giving me this error---> Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'messageanalysis' because it is not full-text indexed. – Saif Inamdar Mar 06 '16 at 14:44
I am not sure if i could understand your question, i have edited my answer to use parameterised query, hope that should work for you. – Vinit Mar 06 '16 at 14:44
check this out for the error - http://stackoverflow.com/questions/6003240/cannot-use-a-contains-or-freetext-predicate-on-table-or-indexed-view-because-it – Vinit Mar 06 '16 at 14:48
Both methods doesnt work.. it gives must declare scalar variable @searchparam – Saif Inamdar Mar 07 '16 at 13:50
by mistake I have used `cmd` instead of `myCommand` to add a parameter, edited my answer, try now.. – Vinit Mar 07 '16 at 17:28
what will this command do exactly..? m unable to understand :( I have enabled full text search to use contains and i have set the data type of table columns as Text.. so i should change it to System.Data.SqlDbType.Text ? and should i remove the execute reader from my program? – Saif Inamdar Mar 08 '16 at 14:07
Correct you should use Text datatype and don't remove ExecuteReader, are you getting any exception now? – Vinit Mar 08 '16 at 15:22
are you able to run this command using SQL management studio `select emotion from messageanalysis where CONTAINS(keyword,'how')` ? – Vinit Mar 08 '16 at 15:24

How to search a word inside a string where word is in a SQL Server table

1 Answers1