1. Strings

A string is a sequence of characters enclosed in single quotes '. For example: 'a string'. Multiple quoted strings concatenated together are equivalent to a single string. The following two lines are equivalent:

'a string'
'a' ' ' 'string'

In a string, specific sequences have special meanings. Each sequence starts with a backslash \ and is known as an escape character. GBase 8a MPP Cluster supports the following escape characters:

These symbols are case-sensitive. For example, \b is interpreted as a backspace, but \B is interpreted as B.

For other escape sequences, the backslash is ignored. In other words, the backslash is used to interpret escape characters and is not escaped itself.

When a string contains quotes:

1) If a string is quoted with single quotes ', any single quote ' character within the string can be escaped using two single quotes ''. Alternatively, a backslash \ can be used to escape the quote.
2) If a string is quoted with single quotes ', double quotes " within the string do not need special treatment and do not need to be escaped or repeated.

Examples

Example 1: Strings enclosed in single quotes '.

gbase> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello' FROM dual;
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
1 row in set

Example 2: Strings containing the escape character \.

gbase> SELECT 'This\nIs\nFour\nLines' FROM dual;
+--------------------+
| This
Is
Four
Lines |
+--------------------+
| This
Is
Four
Lines |
+--------------------+
1 row in set

Example 3: Backslash is ignored when it has no escape meaning.

gbase> SELECT 'disappearing\ backslash' FROM dual;
+------------------------+
| disappearing backslash |
+------------------------+
| disappearing backslash |
+------------------------+
1 row in set

If you want to insert binary data into a BLOB field, the following characters must be represented using escape characters:

Example 4: The productBlob field in the created table is of type BLOB, and the inserted data contains escape characters.

gbase> DROP TABLE IF EXISTS products;
Query OK, 0 rows affected

gbase> CREATE TABLE products (productBlob BLOB);
Query OK, 0 rows affected

gbase> INSERT INTO products VALUES('abdcdrf\\ghi\'jklm\"nopqrs\0tuvwxyz');
Query OK, 1 row affected

gbase> SELECT productBlob FROM products;
+---------------------------------+
| productBlob                     |
+---------------------------------+
| abdcdrf\ghi'jklm"nopqrs tuvwxyz |
+---------------------------------+
1 row in set

When writing code, any string might contain these special characters, so they must be escaped before being passed as data in an SQL statement to GBase 8a MPP Cluster.

2. Numbers

Integers are represented as sequences of digits. Floating-point numbers use . as a decimal separator. Both number types can be prefixed with - to indicate a negative value.

Examples of valid integers: 1221, 0, -32

Examples of valid floating-point numbers: -32032.6809E+10, 148.00E+13

3. Hexadecimal Values

GBase 8a MPP Cluster supports hexadecimal values. In numeric contexts, they are used as equivalent integers.

In string contexts, they are treated as strings, with each pair of hexadecimal digits interpreted as a character with the corresponding ASCII code.

Examples

Example 1: 0xa is equivalent to the integer 10.

gbase> SELECT 0xa+1 FROM dual;
+-------+
| 0xa+1 |
+-------+
|    11 |
+-------+
1 row in set

Example 2: Convert 4742617365 into its corresponding ASCII code.

gbase> SELECT x'4742617365' FROM dual;
+---------------+
| x'4742617365' |
+---------------+
| GBase         |
+---------------+
1 row in set

Example 3: Convert 5061756c into its corresponding ASCII code.

gbase> SELECT 0x5061756c FROM dual;
+------------+
| 0x5061756c |
+------------+
| Paul       |
+------------+
1 row in set

The expression x'hexstring' is based on standard SQL, while the expression 0x is based on ODBC. Both are equivalent.

Example 4: The HEX() function can convert a string or numeric value into a hexadecimal formatted string.

gbase> SELECT HEX('cat') FROM dual;
+------------+
| HEX('cat') |
+------------+
| 636174     |
+------------+
1 row in set

gbase> SELECT 0x636174 FROM dual;
+----------+
| 0x636174 |
+----------+
| cat      |
+----------+
1 row in set

4. Boolean Values

The constant TRUE is equivalent to 1, and the constant FALSE is equivalent to 0. These constants are case-insensitive.

Example

Example 1: Query the values corresponding to TRUE and FALSE.

gbase> SELECT TRUE, true, FALSE, false FROM dual;
+------+------+-------+-------+
| TRUE | TRUE | FALSE | FALSE |
+------+------+-------+-------+
|    1 |    1 |     0 |     0 |
+------+------+-------+-------+
1 row in set

5. NULL Values

NULL is case-insensitive.

Note: NULL is different from the numeric type 0 or the string type empty string.

That's all about constants. Thank you for reading!