Hello, I’m connecting Navision with ASP.NET but if I run a query with the LIKE statement I get a case sensitive search. Is there a function to make the LIKE case unsensitive? Regards, Hans
Use CONTAINS in the WHERE clause. Look for the word CONTAINS here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_154e.asp If you are lost in the MSDN maze, follow the link below for the description of SELECT, then follow, WHERE, Search Conditions, then CONTAINS. When you are in the CONTAINS page, use Find(Ctrl+F) in your browser to look for the word “case”. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_154e.asp
Are you talking Navision Server and C/ODBC, or SQL Server and the SQL Server ODBC driver?
Navision Server and C/ODBC. My query: SELECT Name FROM Contact WHERE Name LIKE ‘%"+ [keyword] +"%’ ORDER BY No_ I also tried the @: SELECT Name FROM Contact WHERE Name LIKE ‘%@"+ [keyword] +"%’ ORDER BY No_
I’ve checked the CONTAINS function, but C/ODBC doens’t support it. Do you have another option?
I don’t have C/ODBC manual at the moment, but there are some functions usually used in MS SQL, I don’t think that C/ODBC supports them, but you could try. See PATINDEX and LOWER/UPPER here: String Functions: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp Also try a search with a fixed string if you haven’t done that already…
I’ve tried the LOWER/UPPER, PATINDEX function but C/ODBC doens’t support it. What do you mean by a fixed string?
I meant a fixed search string such as “abc”: sql = “SELECT Name FROM Contact WHERE Name LIKE ‘%abc%’ ORDER BY No_”
I’ve tried the query with a fixed string to test but with no result.
Use the “Search Name” field instead of the Name field. Your SQL statement should look like this: keyword = UCase(keyword) ’ ← This is a VBScript function. sql = “SELECT Name FROM Contact WHERE ““Search Name”” LIKE '%”+ keyword +"%’ ORDER BY No_" Note the use of 2 double-quote characters. The first is the escape character. By default, whenever the user changes “Name”, it’s copied over to “Search Name”. However, if the user, changes “Search Name”, the changes are not reflected back to the “Name” field. To be sure that both fields are identical, make an MS Access query to count those records that have different contents in these fields.
I know I can do a search on the search name column but that was not the solution I was looking for. I’ve found also another solution: First; select all names from contact and bind the data in a DataTable. Second; filter the column name in the DataTable with a LIKE statement because its not case sensitive. This last solution is very slow because I’m testing on a table with more than 70.000 records. I hope there is another solution thats faster… Help
At this time I’m experiencing the same problem. Does anybody already have a solution that works?
Hi Hans, For performance issues, using the LIKE operator in a query on 70 000 records is not a good idea. The query will have no choice to do a text compare on each records, which could be very slow. It would be much better if you already filtered on another field to have less record, or, for example, if you know the first letter of the text you are looking for.
We did something similar for one client who needed to search on a text field. We created a new field of type Code that was updated with the value in the text field. Then you can search on your new field but display the text field. Regards, Django