Case-Insensitive querying with SQL Server 2012

Stetson

When searching for matches for user-entered AwareIM form fields in an external SQL Server 2012 db, what's the best way to make the query case-insensitive?

The following statements (using SQL Server functions and AwareIM functions respectively) generate the error "Internal error. Unable to convert query condition":

FIND Param2 WHERE upper(Param2.VENDOR_VNAME) CONTAINS upper(Param1.VenName1)
FIND Param2 WHERE to_upper_case(Param2.VENDOR_VNAME) CONTAINS to_upper_case(Param1.VenName1)

The following statement only finds matches when the case in the db EXACTLY matches the case entered by the user on the AwareIM form (which is not what I need):

FIND Param2 WHERE Param2.VENDOR_VNAME CONTAINS Param1.VenName1

How do I make this search case-insensitive? Thanks.

Jaymer

there's a MSSQL setting to be case insensitive.
thats where you need to do this - and you never need to worry about what you are trying in your example, OR user filtering.

Stetson

Music to my ears. Thanks so much, Jaymer!

Stetson

Jaymer, I'm sorry - are you referring to a setting within AwareIM or SQL Server?

Stetson

Jaymer, if you're referring to a setting within SQL Server, this is not something we can change. Is there a way to implement case-insensitive SQL Server searching from within AwareIM?

Thanks again.

aware_support

FIND Param2 WHERE to_upper_case(Param2.VENDOR_VNAME) CONTAINS to_upper_case(Param1.VenName1)

This should work in theory. Can you check SQL that this gets translated into (you can enable SQL in the Log Settings if running 8.4). Maybe there is a bug somewhere. What exactly is the error message you are getting?

Stetson

I enabled SQL in our log settings, however no SQL gets generated for this FIND statement. Only the error "Internal error. Element TO_UPPER_CASE in query condition …<query>... cannot be converted to the currently installed version of SQL".

Our SQL Server version is 2012 SP1, 11.0.3156.0 (X64).

Is there a workaround? Please advise. The case of the data being searched varies in our DB due to mergers and system consolidations, so case-insensitive searching is essential in order for this AwareIM application to be successful.

Thanks very much.

aware_support

This works for us. The error message you are getting indicates that there is something wrong with the name of the TO_UPPER_CASE function. Are you sure you are spelling it right in your BSV?

This is the query we are using and it works:
FIND T2 WHERE TO_UPPER_CASE(T1.Name) CONTAINS TO_UPPER_CASE(T2.Name)

If you are sure your query is correct prepare a small BSV that demonstrates the problem and send it to support@awareim.com describing the steps required to reproduce the problem in this BSV.

Stetson

Sending the BSV. Thank you.

aware_support

The issue has nothing to do with the TO_UPPER_CASE function. It is because the function is applied to an object stored externally. Currently you cannot use any Aware IM functions in queries if a query is on an external object. The workaround is to use a stored procedure.

Stetson

Thank you.