How to search more than a word in a column SQL database?

5 pts.
Tags:
Python
SQL
SQL commands
SQL Database
i have an array of words to be search in my sql database like:
searchword = array(['Arrays','Logistics'])
for word in searchword:
    conn.BeginTrans()
    cursor = conn.Execute("SELECT idf FROM instance WHERE instance='"+ word +"'")
    conn.CommitTrans()
but some of the searchword cannot be access eventhough it do exist in my database table. what is the specific sql command to search it? pls assist.
ASKED: July 26, 2011  5:19 AM
UPDATED: March 31, 2012  8:14 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You would need to use the IN clause.. The SELECT statement will need to like something like this.
<pre>
SELECT idf
FROM instance
WHERE instance IN (‘Arrays’,'Logistics’)</pre>

Your python code should look something like this.
<pre>searchword = array(['Arrays','Logistics'])

for word in searchword:

conn.BeginTrans()

cursor = conn.Execute(“SELECT idf FROM instance WHERE instance=(“+ word +”)”)

conn.CommitTrans()</pre>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    What is contained in the column containing the word(s) of interest? If you use the "=" (equals) operator, the column must contain exactly one word, with no leading/trailing other text, spaces, etc. i.e. if you are searching for the word "Arrays" in the table, then the column being searched must contain just that word "Arrays". If it contains something like "Arrays are fun", then your search will fail. If the column of data contains longer text, you can find "substrings" matching your query word, but you must beware of false positives. To search for a substring, use: select ... from mytable where mycolumn LIKE '%' + word + '%'. Unfortunately, if you are looking for the "word" "ARE", this will find the following (and many others): "are" "many records are found" "how many are there?" "bewARE of these substrings" - oops Handling this in T-SQL is quite a pain, but luckily there is a better solution. I would suggest Googling "SQL Server full text search". SQL Server has a built-in ability to generate full text search data and then you can search it using the CONTAINS predicate. Full text searching is how most systems provide the ability to find individual "words" inside longer text strings, and there are a lot more useful features available as well.
    3,830 pointsBadges:
    report
  • TomLiotta
    First question should be about what DBMS holds the database? Then, what is the definition of the column being searched, e.g., is it CHAR, VARCHAR or perhaps something more complex such as a CLOB? Does the column contain individual words or is it phrases? And is the search case-sensitive? Tom
    125,585 pointsBadges:
    report
  • carlosdl
    I don't think the phyton code suggested by Mrdenny is going to work. I'm not a phython expert, but I suppose this expression:
    for word in searchword
    is creating a loop with as much iterations as elements in the array, so every time the query is executed it will look for a single word, making the IN clause and the parenthesis unnecessary. IMO, the key to solve the problem would be in the answer to the questions posted by Tom, and the observations made by Kccrosser.
    65,110 pointsBadges:
    report
  • Semantic85
    [...] How to search more than a word in a column SQL database? [...]
    0 pointsBadges:
    report
  • Semantic85
    [...] 1. Mrdenny, Kccrosser, TomLiotta, and Carlosdl had some tips and code for a member wondering how to search more than a word in a column SQL database. [...]
    0 pointsBadges:
    report
  • dil1234
    Can anybody help me with the coding
     Dim tbStudent As DataTable
            Dim dcPrimaryKey(0) As DataColumn
            tbStudent = sqlDataset.Tables("Major")
            dcPrimaryKey(0) = tbStudent.Columns("MajorName")
            tbStudent.PrimaryKey = dcPrimaryKey
         

            Dim major As String = TextBox1.Text
            Dim depa As String = TextBox2.Text
            Dim sname As String = TextBox3.Text



            Dim frmFind As New Form4
            Dim strStudentNo As String
            frmFind.ShowDialog()

            strStudentNo = frmFind.majorname
            If Not major Is Nothing Then
                Dim drSubject As DataRow = tbStudent.Rows.Find(major)
                If Not major Is Nothing Then
                    TextBox1.Text = drSubject.Item("MajorName")
                    TextBox2.Text = drSubject.Item("Departement")

                    TextBox3.Text = drSubject.Item("Suject_Name")
                Else
                    MessageBox.Show("Student not found.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            End If

        End Sub


        Private Sub ShowREC()
            Dim sqlDataset As New DataSet

            Dim SQLdr As DataRow
            If n >= 0 Then
                '  SQLdr = sqlDataset.Tables("Major").Rows(n)
                SQLdr = sqlDataset.Tables("Major").Rows(n)

                TextBox1.Text = SQLdr.Item("MajorName")
                TextBox2.Text = SQLdr.Item("Departement")

                TextBox3.Text = SQLdr.Item("Suject_Name")
                'dtpDOB.Value = dr1.Item("DOB")
                'cmbMajorI.Text = dr1.Item("MajorI")
                'cmbMajorII.Text = dr1.Item("MajorII")

            End If
    40 pointsBadges:
    report
  • TomLiotta
    Can anybody help me with the coding   Can you explain how this relates to this thread? Anhd can you tell us what problem you have?   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following