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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.