{question}
How should "COLLATION" behave when using it in a table definition?
{question}
{answer}
Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database. NuoDB supports currently supports two types of collation: 1. (case sensitive) which is default and (2.) 8859-1U (case insensitive).
Here is an example of how "COLLATION" behaves when using it in a table definition:
Create a table using case insensitive collation
create table ColTest(MyTextCI nvarchar(100) COLLATION "8859-1U");
Insert data into the table:
insert into ColTest(MyTextCI) values ('AbCd');
insert into ColTest(MyTextCI) values ('ABCD');
insert into ColTest(MyTextCI) values ('abcd');
This query should return one row and count = 3 for insensitive collation:
SQL> select MyTextCI,count(*) from ColTest group by MyTextCI; MYTEXTCI COUNT
--------- ------ AbCd 3
This query should also return just one row:
SQL> select distinct MyTextCI from ColTest; MYTEXTCI
--------- AbCd
More information and examples can be found here.
{answer}
Comments