Split words after predefined symbol into more than search query & term

Question

0.00/5 (No votes)

See more:

I'm working on a simple translation application based on C# & SQL Server CE 3.5

I have a search textbox that searches certain columns in database through `textBox1.Text` with normal SQL query [`SELECT.. LIKE '% %'`]

----------

*What I want to achieve :*
**I want to search for all the words after certain symbols (+ for example) in all locations in database , so they don't need to be written in the full context** (word after word as they exist in database)

*In other words :*
**I want to split words after certain symbols , so that the program search for each word independently** (search the word before symbol and each word after symbol separately)

----------

***Example:***
If I tried to search for the value "burden of proof" , I've to write it in the previous context, but for the user this will not apply. So I want him to put a symbol in-between the two words he is willing to search for (namely he should search for "burden+proof")

Picture 1 : http://i.imgur.com/sd5Y5B7.jpg , Picture 2 : http://i.imgur.com/gVj41xP.jpg

----------

Edit - my search button code :

sqlcmd = new SqlCeCommand
("SELECT * FROM T1 WHERE EnglishWord like '%" + textBox1.Text + "%' OR EnglishDesc like '%" + textBox1.Text + "%' OR ArabicWord like '%" + textBox1.Text + "%' OR ArabicDesc like '%" + textBox1.Text + "%' ", sqlcon);

try
{
listView1.Items.Clear();
sqldr = sqlcmd.ExecuteReader();

while (sqldr.Read())
{

ListViewItem item = new ListViewItem(sqldr["ID"].ToString());
item.SubItems.Add(sqldr["EnglishWord"].ToString());
item.SubItems.Add(sqldr["EnglishDesc"].ToString());
item.SubItems.Add(sqldr["ArabicDesc"].ToString());
item.SubItems.Add(sqldr["ArabicWord"].ToString());
item.SubItems.Add(sqldr["Subject"].ToString());

listView1.Items.Add(item);
}
listView1.Enabled = true;
label7.Text = listView1.Items.Count.ToString();
}
catch (SqlCeException ex)
{
MessageBox.Show("Error: " + ex.Message, "Something wrong");
}

Posted 7-Apr-15 1:59am

Sherif Kamel

Add a Solution

Comments

Richard Deeming 7-Apr-15 10:40am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Maciej Los 7-Apr-15 13:42pm

So, you need to pass comma separated values into in clause, for example:
SELECT <field_list> FROM TableName WHERE Field1 IN ('word1', 'word2')

Sascha Lefèvre 7-Apr-15 20:50pm

I guess Solution 1 will work for you but maybe you should take a look at Lucene.Net:
https://www.google.com/search?q=codeproject+lucene.net&ie=utf-8&oe=utf-8

2 solutions

Solution 2

Please, read my comment to the question.

I'd suggest to read these:
1) Using comma separated value parameter strings in SQL IN clauses[^]
2) Converting comma separated data in a column to rows for selection[^]

Posted 7-Apr-15 8:01am

Maciej Los

Updated 7-Apr-15 20:19pm

v2

Comments

Sascha Lefèvre 7-Apr-15 20:45pm

Sql CE doesn't have FTS as far as I know :)

Maciej Los 8-Apr-15 2:21am

This part of my answer has been removed. Thanks, Sascha ;)

Sherif Kamel 8-Apr-15 10:55am

Thanks for your help. I'll read them carefully

Maciej Los 8-Apr-15 11:19am

You're very welcome ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-04-07T04:54:00

Assuming you want to find all records where each word appears in at least one of the four columns, something like this should work:

C#

sqlcmd = sqlcon.CreateCommand();

string[] words = textBox1.Text.Split(new[] { '+' }, StringSplitOptions.RemoveEmptyEntries);
StringBuilder query = new StringBuilder("SELECT * FROM T1 WHERE 1 = 1");

for (int index = 0; index < words.Length; index++)
{
    string wordToFind = words[index];
    string parameterName = "@word" + index;
    sqlcmd.Parameters.AddWithValue(parameterName, "%" + wordToFind + "%");
    query.AppendFormat(" AND (EnglishWord Like {0} OR EnglishDesc Like {0} OR ArabicWord Like {0} OR ArabicDesc Like {0})", parameterName);
}

sqlcmd.CommandText = query.ToString();

This will also fix the SQL Injection[^] vulnerability in your code.

If the user searches for burden+proof, the resulting query will look something like this:

SQL

SELECT
    *
FROM
    T1
WHERE
    1 = 1
And
    (
        EnglishWord Like @word0 
    Or 
        EnglishDesc Like @word0 
    Or 
        ArabicWord Like @word0 
    Or 
        ArabicDesc Like @word0
    )
And
    (
        EnglishWord Like @word1 
    Or 
        EnglishDesc Like @word1 
    Or 
        ArabicWord Like @word1 
    Or 
        ArabicDesc Like @word1
    )

/*
Parameters:
- @word0 = '%burden%'
- @word1 = '%proof%'
*/