how to know if a column is varchar or varchar_ignorecase?

[tsukasa]

hi, that's the question. I always used VARCHAR_IGNORECASE so I never
asked myself the question, now I would like to know that, if it is
possible. Thanks.

[tsukasa]

another question,

SET IGNORECASE TRUE works changing the table definition of a new table
introducing VARCHAR_IGNORECASE instead of VARCHAR.
It also changes ALTER TABLE COLUMN statements? for any table? or for
the newly created tables?? supossing it is true for any table, the
documentation seems imprecise right?, please clarify me the behaviour,
many thanks!!

[Thomas Mueller]

Hi,

Sorry for the late answer! I forgot to send my response... For a
ResultSet, use ResultSetMetaData.getTypeName, or in the H2 Console:

@META select 'a', cast('a' as varchar_ignorecase);

see column typeName. Using INFORMATION_SCHEMA.COLUMNS,
see column TYPE_NAME:

SELECT TABLE_NAME, COLUMN_NAME, TYPE_NAME FROM INFORMATION_SCHEMA.COLUMNS

I hope this helps,
Thomas

[Thomas Mueller]

Hi,

From http://www.h2database.com/html/grammar.html#sql79

If IGNORECASE is enabled, text columns in newly created tables will be
case-insensitive. Already existing tables are not affected.
This setting is persistent.
The effect of case-insensitive columns is similar to using a collation
with strength PRIMARY.
Case-insensitive columns are compared faster than when using a collation.
Admin rights are required to execute this command.

> SET IGNORECASE TRUE works changing the table definition of a new table
> introducing VARCHAR_IGNORECASE instead of VARCHAR.
> It also changes ALTER TABLE COLUMN statements? for any table? or for
> the newly created tables??

As the documentation says, only newly created tables are affected.

> supossing it is true for any table, the
> documentation seems imprecise right?

I think it is clear, but I am not a native English speaker / writer...

Thomas

[tsukasa]

aaa am sorry I didn't realize I could get the specific TYPE_NAME, doh,
thanks.
I still have a question about the alter column but I can check it
myself I know. When in ignorecase mode, and doing ALTER TABLE COLUMN
for a new table, setting a column as varchar, it is converted to
varchar_ignore_case right? but if I do later SET IGNORECASE FALSE and
SET IGNORECASE TRUE, and then do an ALTER TABLE COLUMN setting that
column as varchar, will leave it as varchar only now? thanks again.

[Thomas Mueller]

Hi,

Calling SET IGNORECASE will effect columns when altering a table. By
the way, there is also VARCHAR_CASESENSITIVE if you want to avoid
that.

Regards,
Thomas