SQL search question
-
I have the following table in MySQL: table name : contacts Field: contact_name company_name service_offered I want the user to be able to search the contact table in this way: if the user entered John Dell then it should search all fields for any of the given words so if John is the first name and Dell is company or John Dell is the name (contains) or John is the name and Dell is the service etc... can anyone tell me how can I do such SQL? Thanks, Jassim
Technology News @ www.JassimRahma.com
-
I have the following table in MySQL: table name : contacts Field: contact_name company_name service_offered I want the user to be able to search the contact table in this way: if the user entered John Dell then it should search all fields for any of the given words so if John is the first name and Dell is company or John Dell is the name (contains) or John is the name and Dell is the service etc... can anyone tell me how can I do such SQL? Thanks, Jassim
Technology News @ www.JassimRahma.com
-
I have the following table in MySQL: table name : contacts Field: contact_name company_name service_offered I want the user to be able to search the contact table in this way: if the user entered John Dell then it should search all fields for any of the given words so if John is the first name and Dell is company or John Dell is the name (contains) or John is the name and Dell is the service etc... can anyone tell me how can I do such SQL? Thanks, Jassim
Technology News @ www.JassimRahma.com
Dear Jassim, If you know the exact column list to search data For Example : 1. company_name 2. first_name 3. last_name Then you can write down your query as below :
SELECT * FROM contacts WHERE (company_name like '%'+@searchText+'%' OR first_name like '%'+@searchText+'%' OR last_name like '%'+@searchText+'%')
Secondly, If you don't know about the column list then let me know the I can write a dynamic query for you.
-
I have the following table in MySQL: table name : contacts Field: contact_name company_name service_offered I want the user to be able to search the contact table in this way: if the user entered John Dell then it should search all fields for any of the given words so if John is the first name and Dell is company or John Dell is the name (contains) or John is the name and Dell is the service etc... can anyone tell me how can I do such SQL? Thanks, Jassim
Technology News @ www.JassimRahma.com
It would be something like this:
SELECT *
FROM contacts
WHERE (contact_name LIKE '%John%' OR contact_name LIKE '%Dell%')
OR (company_name LIKE '%John%' OR company_name LIKE '%Dell%')
OR (service_offered LIKE '%John%' OR service_offered LIKE '%Dell%')Basically, you'd split the search text into separate words and dynamically build the query above to include all the words.
-
It would be something like this:
SELECT *
FROM contacts
WHERE (contact_name LIKE '%John%' OR contact_name LIKE '%Dell%')
OR (company_name LIKE '%John%' OR company_name LIKE '%Dell%')
OR (service_offered LIKE '%John%' OR service_offered LIKE '%Dell%')Basically, you'd split the search text into separate words and dynamically build the query above to include all the words.
the text might be more than two or less... so user might type something like John or John Dell or John Dell international so I want to be able to split all and search
Technology News @ www.JassimRahma.com
-
the text might be more than two or less... so user might type something like John or John Dell or John Dell international so I want to be able to split all and search
Technology News @ www.JassimRahma.com
Yes, and that's what I said. You'll have to build this sql query dynamically on the client by looping through every word in the search text. Something like this (code just out of my mind, not tested).
string BuildQuery(string searchText) {
string sqlQuery = "SELECT * FROM contacts WHERE ";
string[] words = searchText.Split(" ".ToCharArray());
foreach(string word in words) {
sqlQuery+= string.Format("(contact_name LIKE '%{0}%' OR company_name LIKE '%{0}%' OR service_offered LIKE '%{0}%' ) OR ", word);
}
sqlQuery = sqlQuery.SubString(1, sqlQuery.Length-2);
return sqlQuery;
} -
the text might be more than two or less... so user might type something like John or John Dell or John Dell international so I want to be able to split all and search
Technology News @ www.JassimRahma.com
Jassim Rahma wrote:
so I want to be able to split all and search
There's a small difference to keep in mind when using this technique instead of a full-text search; you're effectively selecting ALL textfields for ALL RECORDS. There'll be no optimization on the filter, as the engine will have to check each field whether it 'contains' the request value. That might be rather costly.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]