Skip to Content

HANA db case sensitivity with SQL text searches

Hi folks,

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?

Thanks,

-Patrick

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    May 03, 2013 at 08:58 PM

    Interesting topic.

    After some quick googling, apparently the default behavior varies from DB to DB.

    SQL Server apparently is case insensitive by default, while Oracle apparently isn't.

    One thing the BI layer could do is to use something like

    WHERE UPPER("COLUMN") = UPPER(:criteria)

    In a classic RDBMS, this would have a terrible performance since it wouldn't consider the existing indexes as in the straightforward WHERE clause. In an in-memory DB, though, I'm not really sure what would be the performance degradation (if any). Please test and let us know. 😊

    Add comment
    10|10000 characters needed characters exceeded

  • May 04, 2013 at 09:36 AM

    Hi Patrick,

    In order to avoid case sensitive issues, no need of changing an settings.

    Just proper sql is enough to solve this problem.

    Here is my work around for your situation.

    First i modified the table (EMPLOYEE) to include names in a column "ENAME" with name as JOHN.

    SO i have four employees with the same name but with different cases like all lower case, all upper case, mixed case as shown below

    Then the SQL required to get the solution is

    select "SRK"."EMPLOYEE"."EID","SRK"."EMPLOYEE"."ENAME" from "SRK"."EMPLOYEE"

    WHERE contains ("SRK"."EMPLOYEE"."ENAME", 'john')

    ORDER BY "SRK"."EMPLOYEE"."EID";

    Irrespective of the search string case you give (JOHN, john, JOhn, etc), you will always get it.

    Regards

    Raj Kumar


    Data1.JPG (23.2 kB)
    Data2.JPG (26.2 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Guys, I appreciate all of your great feedback on this. I am digesting all of this information and will discuss with the rest of my HANA developer team before deciding our approach and closing this thread.

      Thanks!

      -Patrick

  • May 03, 2013 at 08:29 PM

    Hi Patrick,

    For text search and non sensitivy you should use:

    CONTAINS("WERKS",'xyz1') ( to catch a word in a phrase )

    or

    CONTAINS("WERKS",'%xyz1%') ( % to works like like and catch part of a word )

    You don't need a text index to try this out, but for special options like fuzzy or performance it's required.

    Regards, Fernando Da Rós

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 14, 2013 at 08:18 PM

    If I need to do group by on any text field and avoid case sensitive Is there any Collate setting in HANA database level?

    Add comment
    10|10000 characters needed characters exceeded