Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1544#discussion_r186333746
--- Diff:
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -581,12 +581,12 @@ characters. See
<<character_value_expressions,Character Value Expressions>>.
[[considerations_for_ascii]]
=== Considerations For ASCII
-For a string expression in the UTF8 character set, if the value of the
+If the value of the
--- End diff --
@DaveBirdsall Thanks for your help :yum:
If I have understood you correctly, do you mean:
ASCII function handles string and column differently:
* For a string, ASCII function accepts only single-byte string expression,
or else it returns the following error:
```
*** ERROR[4106] The character set for the operand of function ASCII must be
ISO88591.
```
For example.
```
SQL>select ascii('æ±') from dual;
*** ERROR[4106] The character set for the operand of function ASCII must be
ISO88591. [2018-05-07 06:01:55]
```
* For a column, the string expression(s) in this column can be only
single-byte,
* For a string expression in the UTF8 character set, the value of the
first byte in the string cannot be greater than 127
* The first byte in the string can be all characters in the ISO88591
character set
or else it returns the following error:
```
*** ERROR[8428] The argument to function ASCII is not valid.
```
For example,
1. Suppose that we have the UTF8 table _t1_ like this:
```
SQL>showddl t1;
CREATE TABLE TRAFODION.SEABASE.T1
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8
COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T1
TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
```
SQL>select * from t1;
C1
----------------
ñ
--- 1 row(s) selected.
```
Select the column c1 and the ASCII function returns the error 8428 because
the ñ (UTF8) is not single-byte .
```
SQL>select ascii(c1) from t1;
*** ERROR[8428] The argument to function ASCII is not valid. [2018-05-07
02:13:42]
```
2. Suppose that we have the ISO88591 table _t2_ like this:
```
SQL>showddl t2;
CREATE TABLE TRAFODION.SEABASE.T2
(
C1 CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T2
TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
```
SQL>select * from t2;
C1
----
ñ
--- 1 row(s) selected.
```
Select the column c1 and return the ASCII code of the first character ñ:
```
SQL>select ascii(c1) from t2;
(EXPR)
----------
241
--- 1 row(s) selected.
```
3. For a UTF8 table contains multiple rows, all string expressions must be
single-byte, or else it returns the following error:
```
*** ERROR[8428] The argument to function ASCII is not valid.
```
* Suppose that we have the UTF8 table _t6_ like this:
```
SQL>showddl t6;
CREATE TABLE TRAFODION.SEABASE.T6
(
C1 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T6
TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
```
SQL>select * from t6;
C1
--------------------
a
ñ
--- 2 row(s) selected.
```
The ASCII function returns the error 8428 because ñ (UTF8) is not
single-byte.
```
SQL>select ascii(c1) from t6;
*** ERROR[8428] The argument to function ASCII is not valid. [2018-05-07
03:11:58]
```
* Suppose that we have the UTF8 table _t8_ like this:
```
SQL>showddl t8;
CREATE TABLE TRAFODION.SEABASE.T8
(
C1 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T8
TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
```
SQL>select * from t8;
C1
----------------
a
b
--- 2 row(s) selected.
```
Select the column c1 and return the ASCII codes of the first characters for
all string expressions in the c1:
```
SQL>select ascii(c1) from t8;
(EXPR)
----------
97
98
--- 2 row(s) selected.
```
4. For a ISO88951 table contains multiple rows, the ASCII function returns
the first ASCII codes for all string expressions in the selected column.
* Suppose we have the ISO88591 table _t7_ like folliowing:
```
SQL>showddl t7;
CREATE TABLE TRAFODION.SCH.T7
(
C1 VARCHAR(4) CHARACTER SET ISO88591
COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T7
TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
```
SQL>select * from t7;
C1
----
a
ñ
--- 2 row(s) selected.
```
```
SQL>select ascii(c1) from t7;
(EXPR)
----------
97
241
--- 2 row(s) selected.
```
---
