Case-Insensitive querying with SQL Server 2012

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Case-Insensitive querying with SQL Server 2012

Post by 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
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Case-Insensitive querying with SQL Server 2012

Post by 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by Stetson »

Music to my ears. Thanks so much, Jaymer!
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by Stetson »

Jaymer, I'm sorry - are you referring to a setting within AwareIM or SQL Server?
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by 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
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Case-Insensitive querying with SQL Server 2012

Post by 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?
Aware IM Support Team
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by 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
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Case-Insensitive querying with SQL Server 2012

Post by 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 [email protected] describing the steps required to reproduce the problem in this BSV.
Aware IM Support Team
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by Stetson »

Sending the BSV. Thank you.
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Case-Insensitive querying with SQL Server 2012

Post by 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.
Aware IM Support Team
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: Case-Insensitive querying with SQL Server 2012

Post by Stetson »

Thank you.
Post Reply