Skip to Content

Case Sencitivity ASA12

Hi
I have my db without case Sensitivity option.
And my example:

create table test (desc varchar(5));

insert into desc values (‘a’);
insert into desc values (‘A’);

Result of sql: select * from test
where desc = ‘A’
is:
a
A

I would like to have only ‘A’.
Is it possible to change db option without generate a new db.

Regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 24, 2016 at 07:49 AM

    If you want to have "case sensitivity" for just one particular query, you can do so without rebuilding the database, namely with the help of the builtin COMPARE function and the "collation tailoring" feature:

    (BTW, your sample code is not really working, the INSERT statements reference the column name instead of the table name, and "desc" is a reserved word and must be quoted.)

    create table test ("desc" varchar(5));
    
    insert into test values ('a');
    insert into test values ('A');
    insert into test values ('ä'); -- add accented char (umlaut)
    insert into test values ('Á');
    
    -- lists all four chars in a case-insensitive database
    select * from test
    where "desc" = 'A';
    
    -- Test for equality based on UCA collation - lists just 'A'
    list just 'A'
    select * from test
    where compare("desc", 'A', 'UCA(locale=en;case=respect;accent=respect)') = 0;
    
    -- Test for equality based on default 1252LATIN1 with case respect - list 'A' and 'Ä' here (i.e. does ignore accents)
    select * from test
    where compare("desc", 'A', '1252LATIN1(case=respect)') = 0;
    
    
    
    
    

    Some further uses can be found here

    Can I use collation-tailoring without the COMPARE (or SORTKEY) function?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2016 at 12:42 AM

    Hi Józef,

    There is no way.

    It is not possible to change the case sensitivity after the database has been created without rebuilding the database.
    http://dcx.sap.com/index.html#1201/en/dbadmin/natlang-s-7673792.html

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2016 at 12:44 PM

    Thank you, Koichi and Volker

    It means I must rebuild my database.

    "desc" of course - You are right. Thank you for your example.

    Regards

    Add comment
    10|10000 characters needed characters exceeded