OledbDriver doing case insensitive LIKE

Question

I am using excel as a database. I am searching through it using a query. I am using LIKE from SQL. I would like to search ignoring the case.

When I try following

...LOWER([COLUMN_NAME]) LIKE "%query%" It throws me the error as IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

When I use [LOWER(COLUMN_NAME)] LIKE "%query%" It gives error as No value given for one or more required parameters.

Can somebody point me to right syntax to use for case insensitive searching ..

Answer 1

Your quoted code is:

LOWER([COLUMN_NAME]) LIKE "%query%"

Those double quotes should probably be single quotes:

LOWER([COLUMN_NAME]) LIKE '%query%'

Edit: I just tried it, and indeed the double quotes caused me to get a similar error to yours. With single quotes, as far as I can tell it doesn't like the use of the function. However, it appears (at least in my case) to be case-insensitive anyway if I just do:

...COLUMN_NAME LIKE '%pattern%'

This is Excel 2007 on Windows XP. I could not find a reference telling me that that behavior was guaranteed, your mileage may vary.

Answer 2

I do a lot of work with excel and to query the data I find this super useful:

http://solidcoding.blogspot.com/2008/01/linq-to-excel-provider-25.html