Case insensitive SELECT.

gromits

Hi,
I'm having trouble with making my SELECT query case-insensitive.
I am using PHP to query a Lotus Domino database through ODBC. Everything works great but it case sensitive. I've tried lots of routes but not found one that works yet. Can anyone help??

The query is:
$query = "SELECT * FROM NCR WHERE DefectItemReturn='Yes' AND Spec_No LIKE '%$spec%' AND CompanyName LIKE '%$company%' AND Region LIKE '%$region%' AND ClearDate Is NOT Null AND Created>='$startdate' and Created<='$enddate' ORDER BY Spec_No";

It's the "Region" bit I want to be case-insensitive.

Cheers
Graeme

Roger_Ramjet

Case-sensitivity, like sort order, is a function of the target database, not php. Don't know Lotus Domino so I can't point you at the run-time parameter that you could use to enforce a case in-sensitive search on a field. In MySQL the default is case in-sensitive and you enforce case sensitivity by using Binary field type or the Binary operator in the query.

Sxooter

Since the lower() and upper() functions are pretty standard, you can make transportable SQL using it:

select * from table where lower(field) = 'abc123';

gromits

Yeah I tried that. but all I get are errors. I think its down to Notes - Its a little quirky🙁

uffis

I was told the SQL functions UPPER() and LOWER() are slow since indexes can't be used with them. No experience in using them whatsoever.

The example was

SELECT lname FROM Users WHERE UPPER(lname) LIKE UPPER('%Foo%')

The original post included another solution for using case-insensitive string comparisons: for each column used for (case-insensitive) search, create another column which includes only upper case strings and create an index for the new column.

Also, for the search column to be more effective, you could only insert data which has no special characters in it in the search column. The special characters must be stripped from the queries as well. That way names like "O'brien" are easily found using a search term such as "obrien", "o'brien" or "O'brien".

If anyone has a better idea, I'm very much open for it.

Roger_Ramjet

Don't know about the speeds of upper() and lower(), would not need to worry about them like this. I would have enforced conversion to lower case/upper case at record creation time.

Names and addresses I force to be saved as upper case simply because they print better, and my apps do a lot of letter printing. UNIX users have the luxury of functions that will capitalise the first letter in each word, I mostly work in windows and can't be bothered. Likewise user initials are uppercase, but usernames would be lower case. With this. the speed of upper() and lower() don't really matter since they are only used once to convert the variable, not many times to convert the corresponding value in each record in the table.

SELECT lname FROM Users WHERE lname LIKE LOWER('%Foo%')

Sxooter

Originally posted by uffis
I was told the SQL functions UPPER() and LOWER() are slow since indexes can't be used with them. No experience in using them whatsoever.

The example was
SELECT lname FROM Users WHERE UPPER(lname) LIKE UPPER('%Foo%')
[/B]

That really depends on the database. I'd not be surprised if Notes couldn't index upper and lower.

PostgreSQL can, as can most commercial SQL engine.

But think of all the problems inherent in trying to do an upper() call on some languages / character sets, where sh becomes that funny looking B in German, etc... Of course it's hard for indexes to work there, so it's common to only index / use indexes on standard ASCII encoding.

gromits

I think its a Notes thing. Unfortunately I have no control over the database itself. I t was setup by amateurs! If I had my way it would be ripped out a replaced with something grown up lol!
Anyway I tried all the UPPER(), LOWER() stuff without success. Guess I'll juts have to add a note to the search page saying it is case sensitive - or get PHP to list all the variations in case 😮

Cheers
Gromits