Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Minor |
| Complexity | Unknown |
| Component | DB - Excel |
| Version | 17.0 |
Add Excel connection support for SET COLLATION, SET BINARY_COLLATION
|
6 KB
|
|
137 KB
|
|
28 KB
|
|
191 KB
Do we need to support SET BINARY_COLLATION? We only use the DOUBLE, VARCHAR, TIMESTAMP, and BOOLEAN data types for Excel. We don't use any BINARY data type.
You are right. We don't need BINARY_COLLATION. Just included that for completeness, but looks like we don't need it.
You are right. We don't need BINARY_COLLATION. Just included that for completeness, but looks like we don't need it.
Added SET COLLATION command.
Syntax:
SET COLLATION { OFF | collationName [ STRENGTH { PRIMARY | SECONDARY | TERTIARY | IDENTICAL } ] }
collationName: ALBANIAN, ARABIC, BELARUSIAN, BULGARIAN, CATALAN, CHINESE, CROATIAN, CZECH, DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN, GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN, IRISH, ITALIAN, JAPANESE, KOREAN, LATVIAN, LITHUANIAN, MACEDONIAN, MALAY, MALTESE, NORWEGIAN, POLISH, PORTUGUESE, ROMANIAN, RUSSIAN, SERBIAN, SLOVAK, SLOVENIAN, SPANISH, SWEDISH, THAI, TURKISH, UKRAINIAN, VIETNAMESE.
STRENGTH: PRIMARY is usually case- and umlaut-insensitive; SECONDARY is case-insensitive but umlaut-sensitive; TERTIARY is both case- and umlaut-sensitive; IDENTICAL is sensitive to all differences and only affects ordering.
Examples:
SET COLLATION ENGLISH
SET COLLATION ENGLISH STRENGTH PRIMARY
SET COLLATION OFF
Added SET COLLATION command.
Syntax:
SET COLLATION { OFF | collationName [ STRENGTH { PRIMARY | SECONDARY | TERTIARY | IDENTICAL } ] }
collationName: ALBANIAN, ARABIC, BELARUSIAN, BULGARIAN, CATALAN, CHINESE, CROATIAN, CZECH, DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN, GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN, IRISH, ITALIAN, JAPANESE, KOREAN, LATVIAN, LITHUANIAN, MACEDONIAN, MALAY, MALTESE, NORWEGIAN, POLISH, PORTUGUESE, ROMANIAN, RUSSIAN, SERBIAN, SLOVAK, SLOVENIAN, SPANISH, SWEDISH, THAI, TURKISH, UKRAINIAN, VIETNAMESE.
STRENGTH: PRIMARY is usually case- and umlaut-insensitive; SECONDARY is case-insensitive but umlaut-sensitive; TERTIARY is both case- and umlaut-sensitive; IDENTICAL is sensitive to all differences and only affects ordering.
Examples:
SET COLLATION ENGLISH
SET COLLATION ENGLISH STRENGTH PRIMARY
SET COLLATION OFF
@tariq: after you verify this issue, pls be sure to update our syntax doc as well
@tariq: after you verify this issue, pls be sure to update our syntax doc as well
There are now 3 different ways to do case insensitive text comparisons.
1. On a per-query basis, use the LOWER or UPPER function.
SELECT * FROM Sheet1 WHERE LOWER(Description) LIKE '%test%'
SELECT * FROM Sheet1 WHERE UPPER(Description) LIKE '%TEST%'
2. Use the SET IGNORECASE command.
SET IGNORECASE {TRUE | FALSE}
TRUE - case insensitive
FALSE - case sensitive (default)
3. Use the SET COLLATION command to specify a locale for collation.
See this comment for detail.
Note: SET COLLATION takes precedence over SET IGNORECASE.
There are now 3 different ways to do case insensitive text comparisons.
1. On a per-query basis, use the LOWER or UPPER function.
SELECT * FROM Sheet1 WHERE LOWER(Description) LIKE '%test%'
SELECT * FROM Sheet1 WHERE UPPER(Description) LIKE '%TEST%'
2. Use the SET IGNORECASE command.
SET IGNORECASE {TRUE | FALSE}
TRUE - case insensitive
FALSE - case sensitive (default)
3. Use the SET COLLATION command to specify a locale for collation.
See this comment for detail.
Note: SET COLLATION takes precedence over SET IGNORECASE.
Verified in ADS 17.0.3-3
Please refer attached .xls file(Test.xls).
| Set Collation Command | SQL Statement: | O/P |
|
set collation OFF GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
|
set collation english STRENGTH PRIMARY GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- A A A A a a a a |
|
set collation english STRENGTH SECONDARY GO |
select * from Sheet1 where name like 'a' order by name; GO |
name ------- A A A A a a a a |
|
set collation english STRENGTH TERTIARY GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
|
set collation english STRENGTH IDENTICAL GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
Verified in ADS 17.0.3-3
Please refer attached .xls file(Test.xls).
| Set Collation Command | SQL Statement: | O/P |
|
set collation OFF GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
|
set collation english STRENGTH PRIMARY GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- A A A A a a a a |
|
set collation english STRENGTH SECONDARY GO |
select * from Sheet1 where name like 'a' order by name; GO |
name ------- A A A A a a a a |
|
set collation english STRENGTH TERTIARY GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
|
set collation english STRENGTH IDENTICAL GO |
select * from Sheet1 where name like 'a' order by name; Go |
name ------- a a a a |
Nilesh, your test case doesn't test umlaut sensitivity. The Excel worksheet contains plain A and a characters; there is no umlaut characters.
SELECT * FROM Sheet1 to query the H2 table.Nilesh, your test case doesn't test umlaut sensitivity. The Excel worksheet contains plain A and a characters; there is no umlaut characters.
SELECT * FROM Sheet1 to query the H2 table.
Issue #14285 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build ADS 17.0.3-3 |
| No time estimate |
1 issue link |
relates to #14289
Issue #14289Behavior of SET COLLATION commands. |
Do we need to support SET BINARY_COLLATION? We only use the DOUBLE, VARCHAR, TIMESTAMP, and BOOLEAN data types for Excel. We don't use any BINARY data type.