HANA db case sensitivity with SQL text searches
We have a HANA view that users are consuming via Microstrategy. All fields such as PLANT (WERKS) are case sensitive. For example if the plant is named 'XYZ1' then the following SQL is NOT returning any results;
select * from TABLENAME where WERKS = 'xyz1'
Now my microstrategy developer is saying that other databases he consumes are not case sensitive and this setting is configured typically at the database level. So my question is twofold;
1) Is there a database level setting in HANA for case sensitivity?
2) If this option is available does it really make sense to turn this sensitivity off?
Lars Breddemann replied
Well, the example that I posted actually is SAP HANA code.
One could use it just like that...
In any case, the Microstrategy Developer still will have to adopt his code, to include the UPPER() function, if this is the approach (s)he wants to take.
Investing a bit more effort to use the CONTAINS() function would allow for fuzzy search, similar to what we have with the enhanced F4 value help in Suite on HANA.
With that not only different letter cases would be handled but also common misspellings.