on 2016 Oct 23 9:51 AM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
70 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.