Case unsensitive LIKE query

H_de_Graaf

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

mayer

... use a further field like the matchcode-field (code-field)

eromein

I think you must use the "@" command. Best place to find an answer to this question would be a SQL manual.

H_de_Graaf

I want to use the name field, it is data type 'Text'.
If I search 'de graaf' and the name is 'de Graaf' I'm getting no results because it's case sensitive.

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_

Where can I find the SQL manual? I have the C/ODBC guide but the answer isn't there.

eromein

Oooooh yeah.... the % charater... That must be it, I'm sure that's it... How could I forget.

I developed some site using php and mysql. mysql should be very much the same (the same) as sql. I used this manual :

http://www.mysql.com/doc/en/index.html

here you can find the answer to your question (maybe):

http://www.mysql.com/doc/en/Case_sensitivity.html

Hope it helps

H_de_Graaf

I'm not using MySQL or SQL server I'm using Navision Server C/ODBC.
So not al sql functionality is supported.

eromein

did you try the @ char without the %?

If that;s not it, then I can't help you any further... sry.

Hope you'll find a solution.

H_de_Graaf

Yes I did...
Thanks for your help anyway.

Is there someone else who has the right solution for my problem???

marco.morreale

I have the same problem.

Marco Morreale

dagviggo

I also experience the same problem when searching in php.

<?php
$db = odbc_connect ("Navision", "user", "pass") or die("Unable to connect to ODBC");

$query = "select * from Customer where Name like '$search_str'";
$result = odbc_exec($db, $query);
?>

No matter how I write the search string, it always does a case sensitive search

fb

Force your search string to uppercase, and then search the "Search Name" field.

$search_str = strtoupper($search_str);
$query = "select * from Customer where Search_Name like '$search_str'";

mvdvis

OR in Mysql:
select * from Customer where Search_Name like binary '$search_str'

DOC:
The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
-> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
-> 0

Armonds

a "little bit" late , but I was also searching on this topic. Solution is to use Case Insensitive Collection for SELECT:

SELECT Name FROM Contact WHERE Name LIKE '%"+ [keyword] +"%' COLLATE Latvian_CI_AS ORDER BY No_