Skip to Content
0

Case Sencitivity ASA12

Oct 23, 2016 at 08:51 AM

120

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Volker Barth Oct 24, 2016 at 07:49 AM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Koichi Ogawa
Oct 24, 2016 at 12:42 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Józef Woźniak Oct 24, 2016 at 12:44 PM
0

Thank you, Koichi and Volker

It means I must rebuild my database.

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

Regards

Share
10 |10000 characters needed characters left characters exceeded