Search Widget Case Sensitive For One Layer But Not Another

[JasonMitchell]

Making a simple WebMap for users in the company to look at.  In the search widget, I added two layers (Leases & Wells).  For the lease layer expression, I have;  LEASENO like ('%[value]%') or LESSOR like ('%[value]%') or LESSEE like ('%[value]%') - this works fine.  Upper/Lower doesn't matter.  The table data is in caps and when entering 'miller' as a search item, it returns everything with 'MILLER' just fine.

Now for my wells layer expression, I have;  WellName like ('%[value]%') - however, when testing it out, it only returns any results if the search value is entered as all caps, as the data in the table is. 

I've made sure that my field names are correct and still cannot come up with any reason why one layer will work correctly but the other won't.

I'm sure it's something simple - but would appreciate any feedback/help you may provide.

Thanks.

[AnthonyGiles · Accepted solution]

Jason,

How is your data stored for each of your layers? In my experience, if you are using an enterprise database like SQL server then your searches will not be case sensitive. If your data is in a file geodabase then it will be case sensitive.

Your best solution is to use the upper or lower function on all of your queries

Regards

Anthony

[JasonMitchell]

Jason,

How is your data stored for each of your layers? In my experience, if you are using an enterprise database like SQL server then your searches will not be case sensitive. If your data is in a file geodabase then it will be case sensitive.

Your best solution is to use the upper or lower function on all of your queries

Regards

Anthony

Thanks Anthony -

In this particular case - the Lease layer is on an SDE database on a SQL Server while the Wells layer is being pulled from a GDB for the time being with plans to add it to our SDE soon.

Problem solved - thanks again for the help.

[AnthonyGiles]

Jason,

Please don't forget to mark the post as answered,

Regards

Anthony

[JillHalchin]

I've just run into this problem, but the the case function trick isn't working in all situations.  I've got services from both SDE and file GDBs. With SDE, I have to add a case function to avoid case sensitivity.  But the case function won't work with FGDBs. The Search returns "No features found" regardless of how I enter the text.  This is with Desktop and GIS server 10.1.1.

Jill Halchin
Southeast Archeological Center
National Park Service