SQL Elements

Syntax Elements

Identifiers

An identifier is divided into an ordinary identifier and a delimited identifier.
An ordinary identifier consists of letters or a combination of letters and numbers, and it is used by internally converting all characters to uppercase. Therefore, it is case-insensitive.

The following is an example of an ordinary identifier.

GOLDILOCKS
GoldiLocks

A delimited identifier consists of letters or a combination of letters and numbers enclosed in double quotes ("). Internally, all characters are used exactly as specified. Therefore, when using a delimited identifier, case sensitivity is observed.

The following is an example of a delimited identifier.

"GOLDILOCKS"
"GoldiLocks"

Literals

Literals refer to the representation of non-null values.

Text Literals

Text literals refer to the representation of strings and binary strings.
Use a single quote (') at the beginning and end of a string to represent text literals.
In addition to double quotes ("), all strings except for the single quote (') string can be enclosed in single quotes (').
Write two consecutive single quotes without any spaces in between to use a single quote (') in the string. 
A string can contain a maximum of 4,000 characters.

The following are examples of text literals for strings.

'GOLDILOCKS'
'Sunje''s DBMS'
A binary string of text literals is a string of hexadecimal numbers that starts with x' (X') and ends with '. Each position in a hexadecimal string can only contain characters corresponding to 0-9 and A (a) to F (f). The length of a hexadecimal string must always be an even number as two digits represent one byte. A binary string can contain a maximum of 4,000 characters.

The following are examples of text literals for binary strings.

x'001f'
X'FF0A'
x'aF37BBc013'

Numeric Literals

Numeric literals refer to literals of numeric types, which can include integers or numbers with decimal points. The syntax for numeric literals is as follows:

[ + | - ] <digits> [ . <digits> ] [ E | e [ + | - ] <digits> ] [ f | F | d | D ]

The following are examples of numeric literals.

20
+123.45
0.03
+1.23E-02
-1.5

10f
+123.45F
1.2E-3F
-22d
123.45D
-1.23E+05D

Datetime Literals

Datetime literals refer to literals of the date/time type. 
A datetime value can be specified using a string literal or by converting a character or numeric value to a datetime value using TO_*function (TO_DATE, etc).

Datetime data types include DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE.

Date Literals

Date literals can be written in the form of DATE'string literal' or TO_DATE(string_literal [, format]).

For more information, refer to TO_DATE, Datetime Format String, NLS_DATE_FORMAT.

DATE'2002-07-15'
TO_DATE( '2002-07-15' )
TO_DATE( '15-JUL-02', 'DD-MON-RR' )
TO_DATE( '2002-07-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
TO_DATE( '2002-07-15 13:25:30', 'YYYY-MM-DD HH24:MI:SS' )
gSQL> SELECT TO_DATE( '2000-07', 'YYYY-MM' ) FROM DUAL;
TO_DATE( '2000-07', 'YYYY-MM' )
-------------------------------
2000-07-01
gSQL> SELECT 
      TO_CHAR( DATE'2002-07-15', 'YYYY-MM-DD HH24:MI:SS' ) AS RESULT
      FROM DUAL;
RESULT             
-------------------
2002-07-15 00:00:00
gSQL> SELECT 
      TO_CHAR( SYSDATE, 
               'YYYY-MM-DD HH24:MI:SS' ) AS RESULT_SYSDATE,
      TO_CHAR( TRUNC( SYSDATE ),
               'YYYY-MM-DD HH24:MI:SS' ) AS RESULT_TRUNC_SYSDATE 
      FROM DUAL;
RESULT_SYSDATE      RESULT_TRUNC_SYSDATE
  ------------------- --------------------
  2014-08-19 10:06:49 2014-08-19 00:00:00
gSQL> SELECT 
      TO_DATE( '2002-08-12' ) = 
      TRUNC( TO_DATE( '2002-08-12 23:59:59', 'YYYY-MM-DD HH24:MI:SS' ) )
      AS RESULT FROM DUAL;
RESULT
------
TRUE

Time Literals

Time literals can be written in the form of TIME'string literal' or TO_TIME(string_literal [, format]).
The time type includes hour, minute, second (fractional seconds).
Fractional seconds can be specified with a maximum of six digits.

For more information, refer to TO_TIME, Datetime Format String, NLS_TIME_FORMAT.

TIME'15:30:59.999999'
TO_TIME( '15:30:59.999999' )
TO_TIME( '09.45.03.546873 AM', 'HH12.MI.SS.FF6 AM' )
TO_TIME( '09:45:03', 'HH12:MI:SS' )

Time with Time Zone Literals

Time with time zone literals can be written in the form of TIME'string literal', TIME WITH TIME ZONE'string literal',  TO_TIME_WITH_TIME_ZONE(string_literal [, format] ), or TO_TIME_TZ(string_literal [, format] ).
The time with time zone type includes hour, minute, second (fractional seconds), and time zone offset (time zone hour, time zone minute).
Fractional seconds can be specified with a maximum of six digits.

For more information, refer to TO_TIME_WITH_TIME_ZONE, Datetime Format String, NLS_TIME_WITH_TIME_ZONE_FORMAT.

TIME'15:30:59.999999 +09:00'

TIME WITH TIME ZONE'15:30:59.999999 +09:00'
TO_TIME_WITH_TIME_ZONE( '15:30:59.999999 +09:00' )
TO_TIME_TZ( '15:30:59.999999 +09:00' )
TO_TIME_WITH_TIME_ZONE( '09.45.03.546873 +09:00 AM', 
                        'HH12.MI.SS.FF6 TZH:TZM AM' )

Timestamp Literals

Timestamp literals can be written in the form of TIMESTAMP'string literal' or TO_TIMESTAMP(string_literal [, format] ).
Timestamp type includes year, month, day, hour, minute, second (fractional seconds).
Fractional seconds can be specified with a maximum of six digits.

For more information, refer to TO_TIMESTAMP, Datetime Format String, NLS_TIMESTAMP_FORMAT.

TIMESTAMP'2002-07-15 15:39:59.999999'
TO_TIMESTAMP( '2002-07-15 15:39:59.999999' )
TO_TIMESTAMP( '15-JUL-02 11.06.30.123456 AM', 
              'DD-MON-RR HH12.MI.SS.FF6 AM' )

Timestamp with Time Zone Literals

Timestamp with time zone literals can be written in the form of TIMESTAMP'string literal',  TIMESTAMP WITH TIME ZONE'string literal', TO_TIMESTAMP_WITH_TIME_ZONE(string_literal [, formt] ), or TO_TIMESTAMP_TZ(string_literal [, format]).
Timestamp with time zone type includes year, month, day, hour, minute, second (fractional seconds), time zone offset (time zone hour, time zone minute).
Fractional seconds can be specified with a maximum of six digits.

For more information, refer to TO_TIMESTAMP_WITH_TIME_ZONE, Datetime Format String , NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.

TIMESTAMP'2002-07-15 15:39:59.999999 +09:00'
TIMESTAMP WITH TIME ZONE'2002-07-15 15:39:59.999999 +09:00'
TO_TIMESTAMP_WITH_TIME_ZONE( '2002-07-15 15:39:59.999999 +09:00' )
TO_TIMESTAMP_TZ( '2002-07-15 15:39:59.999999 +09:00' )
TO_TIMESTAMP_WITH_TIME_ZONE( '15-JUL-02 11.06.30.123456 +09:00 AM',
                             'DD-MON-RR HH12.MI.SS.FF6 TZH:TZM AM' )
TO_TIMESTAMP_TZ( '15-JUL-02 11.06.30.123456 +09:00 AM',
                 'DD-MON-RR HH12.MI.SS.FF6 TZH:TZM AM' )

Interval Literals

Interval literals specify a time interval.
Intervals are classified and expressed as follows.

The following is a list of interval types.

Leading precision
• It is the number of digits in the field and can be specified from 2 to 6. If not specified, the default value is set to 2.
• If the leading field value exceeds the specified leading precision, an error will be returned.
Fractional seconds precision
• It is the number of digits for fractional seconds and can be specified from 0 to 6. If not specified, the default value is set to 6.
• If the fractional second field value exceeds the specified fractional seconds precision, it will be rounded off.

For more information, refer to INTERVAL, Precisions and value range of the second or later field in INTERVAL * TO * , NUMTODSINTERVAL, NUMTOYMINTERVAL.

Examples of Using Interval Literals.

The following are examples of using interval literals.

Interval YEAR

The following are examples of using interval YEAR literals.

Interval YEAR literals.

Example

Description

Display string

INTERVAL'1'YEAR

INTERVAL'01-00'YEAR

1 year

+01-00

INTERVAL'100'YEAR

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'YEAR(3)

100 year

+100-00

INTERVAL'+999999'YEAR(6)

999999 year

+999999-00

INTERVAL'-999999'YEAR(6)

-(999999 year)

-999999-00

Interval MONTH

The following are examples of using interval MONTH literals.

Example

Description

Display string

INTERVAL'1'MONTH

INTERVAL'00-01'MONTH

1 month

+00-01

INTERVAL'100'MONTH

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'MONTH(3)

8 year 4 month

+008-04

INTERVAL'+999999'MONTH(6)

83333 year 3 month

+083333-03

INTERVAL'-999999'MONTH(6)

-(83333 year 3 month)

-083333-03

Interval YEAR TO MONTH

The following are examples of using interval YEAR TO MONTH literals.

Example

Description

Display string

INTERVAL'1-06'YEAR TO MONTH

1 year 6 month

+01-06

INTERVAL'1-12'YEAR TO MONTH

The month value exceeded 11, so it returns the error.

-

INTERVAL'100-11'YEAR TO MONTH

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100-11'YEAR(3) TO MONTH

100 year 11 month

+100-11

INTERVAL'+999999-11'YEAR(6) TO MONTH

999999 year 11 month

+999999-11

INTERVAL'-999999-11'YEAR(6) TO MONTH

-(999999 year 11 month)

-999999-11

Interval DAY

The following are examples of using interval DAY literals.

Example

Description

Display string

INTERVAL'1'DAY

INTERVAL'01 00:00:00'DAY

1 day

+01 00:00:00

INTERVAL'100'DAY

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'DAY(3)

100 day

+100 00:00:00

INTERVAL'+999999'DAY(6)

999999 day

+999999 00:00:00

INTERVAL'-999999'DAY(6)

-(999999 day)

-999999 00:00:00

Interval HOUR

The following are examples of using interval HOUR literals.

Example

Description

Display string

INTERVAL'1'HOUR

INTERVAL'00 01:00:00'HOUR

1 hour

+00 01:00:00

INTERVAL'1000'HOUR(3)

It exceeds the leading precision 3, so it returns the error

-

INTERVAL'1000'HOUR(4)

41 day 16 hour

+0041 16:00:00

INTERVAL'+999999'HOUR(6)

41666 day 15 hour

+041666 15:00:00

INTERVAL'-999999'HOUR(6)

-(41666 day 15 hour)

-041666 15:00:00

Interval MINUTE

The following are examples of using interval MINUTE literals.

Example

Description

Display string

INTERVAL'1'MINUTE

INTERVAL'00 00:01:00'MINUTE

1 minute

+00 00:01:00

INTERVAL'12345'MINUTE(4)

It exceeds the leading precision 4, so it returns the error

-

INTERVAL'12345'MINUTE(5)

8 day 13 hour 45 minute

+00008 13:45:00

INTERVAL'+999999'MINUTE(6)

694 day 10 hour 39 minute

+000694 10:39:00

INTERVAL'-999999'MINUTE(6)

-(694 day 10 hour 39 minute)

-000694 10:39:00

Interval SECOND

The following are examples of using interval SECOND literals.

Example

Description

Display string

INTERVAL'1'SECOND

INTERVAL'00 00:00:01.000000'SECOND

1 second

+00 00:00:01.000000

INTERVAL'100'SECOND

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'99.9999999'SECOND

INTERVAL'99.9999999'SECOND(2,6)

The fractional seconds are rounded off to become 100 second, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL'99.9999999'SECOND(3)

1 minute 40 second

+000 00:01:40.000000

INTERVAL'29.506167'SECOND(2, 2)

29.51 second

+00 00:00:29.51

INTERVAL'999999.999999'SECOND(6,6)

11day 13 hour 46 minute 39.999999 second

+000011 13:46:39.999999

INTERVAL'-999999.999999'SECOND(6,6)

-(11day 13 hour 46 minute 39.999999 second)

-000011 13:46:39.999999

Interval DAY TO HOUR

The following are examples of using interval DAY TO HOUR literals.

Example

Description

Display string

INTERVAL'1 23'DAY TO HOUR

INTERVAL'01 23:00:00'DAY TO HOUR

1 day 23 hour

+01 23:00:00

INTERVAL'1 24'DAY TO HOUR

The hour value exceeds 23(invalid), so it returns the error.

-

INTERVAL'100 23'DAY TO HOUR

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100 23'DAY(3) TO HOUR

100 day 23 hour

+100 23:00:00

INTERVAL'+999999 23'DAY(6) TO HOUR

999999 day 23 hour

+999999 23:00:00

INTERVAL'-999999 23'DAY(6) TO HOUR

-(999999 day 23 hour)

-999999 23:00:00

INTERVAL'-999999 +23'DAY(6) TO HOUR

Invalid sign error

-

Interval DAY TO MINUTE

The following are examples of using interval DAY TO MINUTE literals.

Example

Description

Display string

INTERVAL'1 23:59'DAY TO MINUTE

INTERVAL'01 23:59:00'DAY TO MINUTE

1 day 23 hour 59 second

+01 23:59:00

INTERVAL'1 24:59'DAY TO MINUTE

The hour value exceeds 23 (invalid), so it returns the error.

-

INTERVAL'1 23:60'DAY TO MINUTE

The minute value exceeds 59 (invalid), so it returns the error.

-

INTERVAL'100 23:59'DAY TO MINUTE

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100 23:59'DAY(3) TO MINUTE

100 day 23 hour 59 minute

+100 23:59:00

INTERVAL'+999999 23:59'DAY(6) TO MINUTE

999999 day 23 hour 59 minute

+999999 23:59:00

INTERVAL'-999999 23:59'DAY(6) TO MINUTE

-(999999 day 23 hour 59 minute)

-999999 23:59:00

Interval DAY TO SECOND

The following are examples of using interval DAY TO SECOND literals.

Example

Description

display string

INTERVAL '1 23:59:59.999999'DAY TO SECOND

1 day 23 hour 59 minute 59.999999 second

+01 23:59:59.999999

INTERVAL '1 24:59:59.999999'DAY TO SECOND

The hour value exceeds 23, so it returns the error.

-

INTERVAL '1 23:60:59.999999'DAY TO SECOND

The minute value exceeds 59, so it returns the error.

-

INTERVAL '1 23:59:60.999999'DAY TO SECOND

The second value exceeds 60, so it returns the error.

-

INTERVAL '99 23:59:59.9999999'DAY TO SECOND

The fractional seconds are rounded off to become 100 day, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99 23:59:59.9999999'DAY(3) TO SECOND

100 day

+100 00:00:00.000000

INTERVAL '1 11:22:33.567890'DAY(2) TO SECOND(2)

1 day 11 hour 22 minute 33.57 second

+01 11:22:33.57

INTERVAL '+999999 23:59:59.999999'DAY(6) TO SECOND(6)

999999 day 23 hour 59 minute 59.999999 hour

+999999 23:59:59.999999

INTERVAL '-999999 23:59:59.999999'DAY(6) TO SECOND(6)

-(999999 day 23 hour 59 minute 59.999999 hour)

-999999 23:59:59.999999

Interval HOUR TO MINUTE

The following are examples of using interval HOUR TO MINUTE literals.

Example

Description

Display string

INTERVAL'23:59'HOUR TO MINUTE

INTERVAL'00 23:59:00'HOUR TO MINUTE

23 hour 59 minute

+00 23:59:00

INTERVAL'23:60'HOUR TO MINUTE

The minute value exceeds 59, so it returns the error.

-

INTERVAL'100:59'HOUR TO MINUTE

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100:59'HOUR(3) TO MINUTE

4 day 4 hour 59 minute

+004 04:59:00

INTERVAL'+999999:59'HOUR(6) TO MINUTE

41666 day 15 hour 59 minute

+041666 15:59:00

INTERVAL'-999999:59'HOUR(6) TO MINUTE

-(41666 day 15 hour 59 minute)

-041666 15:59:00

Interval HOUR TO SECOND

The following are examples of using interval HOUR TO SECOND literals.

Example

Description

Display string

INTERVAL '23:59:59.999999'HOUR TO SECOND

INTERVAL '00 23:59:59.999999'HOUR TO SECOND

23 hour 59 minute 59.999999 second

+00 23:59:59.999999

INTERVAL '23:60:59.999999'HOUR TO SECOND

The minute value exceeds 59, so it returns the error.

-

INTERVAL '23:59:60.999999'HOUR TO SECOND

The second value exceeds 59, so it returns the error.

-

INTERVAL '99:59:59.9999999'HOUR TO SECOND

The fractional seconds are rounded off to become 100 hour, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99:59:59.9999999'HOUR(3) TO SECOND

4 day 4 hour

+004 04:00:00.000000

INTERVAL '11:22:29.569'HOUR(3) TO SECOND(1)

11 hour 22 minute 29.6 second

+000 11:22:29.6

INTERVAL '+999999:59:59.999999'HOUR(6) TO SECOND(6)

41666 day 15 hour 59 minute 59.999999 second

+041666 15:59:59.999999

INTERVAL '-999999:59:59.999999'HOUR(6) TO SECOND(6)

-(41666 day 15 hour 59 minute 59.999999 second)

-041666 15:59:59.999999

Interval MINUTE TO SECOND

The following are examples of using interval MINUTE TO SECOND literals.

Example

Description

Display string

INTERVAL '15:23.123456'MINUTE TO SECOND

INTERVAL '00 00:15:23.123456'MINUTE TO SECOND

15 minute 23.123456 second

+00 00:15:23.123456

INTERVAL '15:60.123456'MINUTE TO SECOND

The second value exceeds 59, so it returns the error.

-

INTERVAL '99:59.999999'MINUTE TO SECOND(2)

The fractional seconds are rounded off to become 100 minute, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99:59.999999'MINUTE(3) TO SECOND(2)

1 hour 40 minute

+000 01:40:00.00

INTERVAL '+999999:59.999999'MINUTE(6) TO SECOND(6)

694 day 10 hour 39 minute 59.999999 second

+000694 10:39:59.999999

INTERVAL '-999999:59.999999'MINUTE(6) TO SECOND(6)

-(694 day 10 hour 39 minute 59.999999 second)

-000694 10:39:59.999999

Null Value

A null value is an unknown or undefined value. A NULL value can be of any data type. The unknown value for the boolean type is also represented as a null value.
Null is defined as a keyword and is not case-sensitive.

The following is an example of null value representation.

NULL
Null

Comments

Single Line Comment

Single line comments are comments that start with -- or //. Single line comments treat everything from the comment symbol to the end of the line as a comment.

The following is an example of using a single line comment.

gSQL> SELECT I1, -- I2, I3,
2 I4, I5
3 FROM T1;

I1        I4        I5       
--------- --------- ---------
column i1 column i4 column i5

1 row selected.


gSQL> SELECT I1, // I2, I3,
2 I4, I5
3 FROM T1;

I1        I4        I5       
--------- --------- ---------
column i1 column i4 column i5

1 row selected.

Multiple Line Comment

Multiple line comments are comments that start with /* and ends with */. Multiple line comments are defined from /* to */ and can span multiple lines.

The following is an example of using a multiple line comment.

gSQL> SELECT I1, I2, I3, I4, I5
2  /* Output 
3   all columns of TABLE T1 */

4 FROM T1;

I1        I2        I3        I4        I5       
--------- --------- --------- --------- ---------
column i1 column i2 column i3 column i4 column i5

1 row selected.

Hint Comment

A hint comment is a comment that starts with /*+ and ends with */. Hint comments are similar to multiple line comments, but the difference is that the hint comments have + at the beginning. 
Do not use a space between * and +; otherwise, it will be treated as a multiple line comment.

Unlike other comments, hint comments are specifically meant to be used immediately after the SELECT keyword. The processing instructions specified by the user for the GOLDILOCKS optimizer are described in the hint comment. For more information, refer to SQL Hint.

The following is an example of using a hint comment.

gSQL> SELECT /*+ FULL(T1) */ * FROM T1;

I1        I2        I3        I4        I5       
--------- --------- --------- --------- ---------
column i1 column i2 column i3 column i4 column i5

1 row selected.

SQL Reserved Words and Keywords

SQL Reserved Words

GOLDILOCKS supports reserved words that are defined as SQL reserved words. These SQL reserved words can not be used outside of their specified locations.

SQL reserved words can be used as identifiers by enclosing them in double quotes ("), but this practice is not recommended, as it decreases readability.

gSQL> CREATE TABLE "SELECT" ( "FROM" INTEGER );

Table created.

gSQL> INSERT INTO "SELECT" ( "FROM" ) VALUES ( 1 );

1 row created.

gSQL> SELECT "FROM" FROM "SELECT";

FROM
----
   1

1 row selected.
The following are the SQL reserved words for GOLDILOCKS. The words marked with an asterisk (*) are supported by the SQL standard. 
For more information about this list, refer to V$RESERVED_WORDS.
ABSOLUTE
ACCESS
ADMINISTRATION
ALL *
ALLOCATE *
ALTER *
ANALYZE       
AND *
ANTI          
ANY *
ARE *
AS *
ASYMMETRIC *
AT *
AUDIT         
AUTHORIZATION *
BEGIN *
BETWEEN *
BOTH *
BY *
CALL *
CASE *
CHECK *
CLOSE *
CLUSTER            
CLUSTER_GROUP_ID   
CLUSTER_GROUP_NAME 
CLUSTER_MEMBER_ID  
CLUSTER_MEMBER_NAME
CLUSTER_SHARD_ID   
COLUMN *
COMMENT
COMMIT *
CONNECT *
CONNECT_BY_ISCYCLE 
CONNECT_BY_ISLEAF  
CONNECT_BY_ROOT    
CONSTRAINT *
CREATE *
CROSS *
CURRENT *
CURRENT_CATALOG *
CURRENT_DATE *
CURRENT_DEFAULT_TRANSFORM_GROUP *
CURRENT_PATH *
CURRENT_ROLE *
CURRENT_ROW *
CURRENT_SCHEMA *
CURRENT_TIME *
CURRENT_TIMESTAMP *
CURRENT_TRANSFORM_GROUP_FOR_TYPE *
CURRENT_USER *
DATABASE
DEALLOCATE *
DECLARE *
DEFAULT *
DELETE *
DEREF *
DESCRIBE *
DISCONNECT *
DISTINCT *
DROP *
ELSE *
EMPTY       
END *
ESCAPE *
EXCEPT *
EXEC *
EXECUTE *
EXISTS *
FALSE *
FETCH *
FILTER *
FIRST
FOR *
FOREIGN *
FREE *
FROM *
FULL *
FUNCTION *
GET *
GLOBAL *
GRANT *
GROUP *
HAVING *
HOLD *
IDENTIFIED
IF
IMMEDIATE
IN *
INDEX       
INDICATOR *
INNER *
INOUT *
INSERT *
INTERSECT *
INTO *
IS *
JOIN *
LAST
LATERAL       
LEADING *
LEFT *
LEVEL         
LIKE *
LIMIT
LOCAL *
LOCALTIME *
LOCALTIMESTAMP *
LOCAL_OFFLINE 
LOCK          
MATCH *
MEMBER *
MERGE *
MINUS
NATURAL *
NEW *
NEXT
NOAUDIT       
NONE          
NOT *
NULL *
OF *
OFFSET *
OLD *
ON *
OPEN *
OR *
ORDER *
OUT *
OVER            
PACKAGE         
PHYSICAL_PAGE_ID
PREPARE *
PRIMARY *
PRIOR
PROCEDURE *
PROFILE
REF *
REFERENCES *
RELATIVE
RELEASE *
RENAME
RETURN *
RETURNING
RETURNS *
REVOKE *
RIGHT *
ROLLBACK *
ROW *
ROWID
ROWNUM         
ROWS *
ROW_NUMBER *
SAVEPOINT *
SELECT *
SEMI           
SESSION_USER *
SET *
SHARDING_HANDLE
SOME *
SQL *
SQLEXCEPTION *
SQLSTATE *
SQLWARNING *
START *
SYMMETRIC *
SYNONYM
SYSDATE
SYSTEM *
SYSTEM_USER *
SYSTIME
SYSTIMESTAMP
SYS_CONNECT_BY_PATH
TABLE *
THEN *
TO *
TRAILING *
TRIGGER *
TRUE *
TRUNCATE *
UNION *
UNIQUE *
UNKNOWN *
UPDATE *
USAGE       
USER *
USING *
VALUES *
VIEW
WHEN *
WHENEVER *
WHERE *
WINDOW *
WITH *
WITHOUT *

SQL Keywords

GOLDILOCKS SQL keywords are not reserved words. However, they are keywords that are used internally by GOLDILOCKS. Therefore, it is not recommended to use GOLDILOCKS SQL keywords, as doing so can decrease the readability of the results.

The list of GOLDILOCKS SQL keywords can be viewed through V$KEYWORDS.

Compatibility for Syntax Elements

The SQL standard compatibility for syntax element is as follows.

SQL standard compatibility for syntax element

Feature ID

Description

Availability

E021-03

Character literals

O

E131

Null value support (nulls in lieu of values)

O

E161

SQL comments using leading double minus

O

F051-01

DATE data type (including support of DATE literal)

O

F051-02

TIME data type (including support of TIME literal) with fractional seconds precision of at least 0

O

F051-03

TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6

O

F271

Compound character literals

X

F383

Set column not null clause

O

F391

Long identifiers

X

F392

Unicode escapes in identifiers

X

F393

Unicode escapes in literals

X

T023

Compound binary literals

X

T024

Spaces in binary literals

X

T101

Enhanced nullability determination

X

T351

Bracketed comments

X

T591

UNIQUE constraints of possibly null columns

O

X041

Basic table mapping: null absent

X

X042

Basic table mapping: null as nil

X

X051

Advanced table mapping: null absent

X

X052

Advanced table mapping: null as nil

X

X170

XML null handling options

X

X400

Name and identifier mapping

X

Data Type

Numeric Type

Numeric data types are classified based on their storage method and the representation of the fractional part.

Decimal Numeric Type

The precision and scale of this type are based on decimal numbers. Precision, which indicates the accuracy of valid digits, and scale, which defines the range of the fractional part, are based on decimal numbers.

Decimal Fixed-Point Number Type

The decimal fixed-point number type is defined in SQL.

Decimal fixed-point number type

Type

Decimal precision

Decimal scale

Refer to

NUMBER( p )

p

0

NUMBER

NUMBER( p, s )

p

s

NUMBER

NUMERIC( p )

p

0

NUMERIC

NUMERIC( p, s )

p

s

NUMERIC

DECIMAL( p )

p

0

NUMERIC type alias

DECIMAL( p, s )

p

s

NUMERIC type alias

DEC( p )

p

0

NUMERIC type alias

DEC( p, s )

p

s

NUMERIC type alias

SMALLINT

5

0

NUMBER type alias

INTEGER

10

0

NUMBER type alias

BIGINT

19

0

NUMBER type alias

INT2

5

0

NUMBER type alias

INT4

10

0

NUMBER type alias

INT8

19

0

NUMBER type alias

Decimal Floating Point Number Type

The decimal floating-point number type is defined in SQL.

Decimal floating-point number type

Type

Decimal precision

Decimal scale

Refer to

NUMBER

38

N/A

NUMBER

FLOAT( p )

ceil( log10 2p )

N/A

FLOAT

REAL

ceil( log10 224 ) = 8

N/A

FLOAT type alias

DOUBLE

ceil( log10 253 ) = 16

N/A

FLOAT type alias

FLOAT4

ceil( log10 224 ) = 8

N/A

FLOAT type alias

FLOAT8

ceil( log10 253 ) = 16

N/A

FLOAT type alias

Binary Number Type

The precision and scale of this type are based on binary numbers. Precision, which indicates the accuracy of valid digits, and scale, which defines the range of the fractional part, are based on binary numbers.

Binary Fixed-Point Number Type

The binary fixed-point number type refers to the signed integer data type in the C language.
1 bit is used to represent the sign, while the remaining bits are used to represent the precision. However, no bits are used to represent the scale.
Binary fixed-point number type

Type

Binary precision

Binary scale

Refer to

NATIVE_SMALLINT

15

0

NATIVE_SMALLINT

NATIVE_INTEGER

31

0

NATIVE_INTEGER

NATIVE_BIGINT

63

0

NATIVE_BIGINT

Binary Floating-Point Number Type

The binary floating-point type refers to the float and double data types in the C language.
1 bit is used to represent the sign, while the remaining bits are used to represent both the precision and the scale.
Binary floating-point number type

Type

Binary precision

Binary scale

Refer to

NATIVE_REAL

23

8

NATIVE_REAL

NATIVE_DOUBLE

52

11

NATIVE_DOUBLE

The precision and scale of the binary floating-point type can vary depending on the compiler and OS.

CHARACTER STRING Type

CHARACTER STRING data types are classified based on whether they are variable-length strings and the maximum string length.

BINARY STRING Type

BINARY STRING data types are classified based on whether they are variable-length binary strings and the maximum binary string length.

Date/ Time Type

The date/time data type specifies the year, month, day, hour, minute, second, and time zone offset,  according to its representation method. 
The date/time type includes the DATE, TIME, and TIMESTAMP types.

INTERVAL Type

The INTERVAL data type specifies a time interval. 
It specifies the interval in terms of years, months, days, hours, minutes, seconds according to its representation method.
INTERVAL data types are classified into the YEAR TO MONTH family and the DAY TO SECOND family, based on the range of value representation.

BOOLEAN Type

The BOOLEAN data type stores truth values of TRUE, FALSE, and UNKNOWN. The UNKNOWN value is represented as a null value. All expressions used as conditions return a BOOLEAN value, and any column or value defined as a BOOLEAN data type can be used as a condition.
The following literals can be stored in the boolean data type.
For more information, refer to BOOLEAN.

ROWID Type

All records stored in the database have unique location information. The record identifier (ROWID) is used to distinguish each record.
The ROWID data type is used to store and manage the record identifier (ROWID).
The record identifier (ROWID) is obtained by querying the ROWID pseudo column.
For more information, refer to ROWID.

Type Comparison

Comparing two types is done based on a single   representative type. If the  types being compared are different from the representative type, a type conversion may occur.
The representative types for type comparison define the type used as the reference for comparison.

The following table describes the target type conversion for comparison based on each representative type.

The following are the abbreviations used for type comparison.

In the type comparison table, built-in data types are represented by abbreviated words enclosed in double quotes ("").

Representative types for type comparison

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

VC

VC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

VARCHAR

VC

VC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

LONG VARCHAR

LC

LC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

BINARY

VB

VB

LB

VARBINARY

VB

VB

LB

LONG VARBINARY

LB

LB

LB

NATIVE_SMALLINT

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_INTEGER

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_BIGINT

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_REAL

NU

NU

NU

ND

ND

ND

ND

ND

NU

NU

NU

NATIVE_DOUBLE

ND

ND

ND

ND

ND

ND

ND

ND

ND

ND

ND

NUMBER

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

NUMERIC

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

FLOAT

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

DATE

DA

DA

DA

DA

TS

SZ

TIME

TI

TI

TI

TI

TZ

TIME_TZ

TZ

TZ

TZ

TZ

TZ

TIMESTAMP

TS

TS

TS

TS

TS

SZ

TIMESTAMP_TZ

SZ

SZ

SZ

SZ

SZ

SZ

INTERVAL_YM

YM

YM

YM

YM

YM

YM

YM

YM

YM

YM

INTERVAL_DS

DS

DS

DS

DS

DS

DS

DS

DS

DS

DS

BOOLEAN

BO

BO

BO

BO

ROWID

RI

RI

RI

RI

Type conversion for VC comparison

Source type

Converted type

CHAR

CHAR (no conversion)

VARCHAR

VARCHAR (no conversion)

Type conversion for LC comparison

Source type

Converted type

CHAR

CHAR (no conversion)

VARCHAR

VARCHAR (no conversion)

LONG VARCHAR

LONG VARCHAR (no conversion)

Type conversion for VB comparison

Source type

Converted type

BINARY

BINARY (no conversion)

VARBINARY

VARBINARY (no conversion)

Type conversion for LB comparison

Source type

Converted type

BINARY

BINARY (no conversion)

VARBINARY

VARBINARY (no conversion)

LONG VARBINARY

LONG VARBINARY (no conversion)

Type conversion for NB comparison

Source type

Converted type

CHAR

NATIVE_BIGINT

VARCHAR

NATIVE_BIGINT

LONG VARCHAR

NATIVE_BIGINT

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

Type conversion for ND comparison

Source type

Converted type

CHAR

NATIVE_DOUBLE

VARCHAR

NATIVE_DOUBLE

LONG VARCHAR

NATIVE_DOUBLE

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

NATIVE_REAL

NATIVE_REAL (no conversion)

NATIVE_DOUBLE

NATIVE_DOUBLE (no conversion)

NUMBER

NUMBER (no conversion)

NUMERIC

NUMERIC (no conversion)

FLOAT

FLOAT (no conversion)

Type conversion for NU comparison

Source type

Converted type

CHAR

NUMBER

VARCHAR

NUMBER

LONG VARCHAR

NUMBER

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

NATIVE_REAL

NATIVE_REAL (no conversion)

NATIVE_DOUBLE

NATIVE_DOUBLE (no conversion)

NUMBER

NUMBER (no conversion)

NUMERIC

NUMERIC (no conversion)

FLOAT

FLOAT (no conversion)

Type conversion for DA comparison

Source type

Converted type

CHAR

DATE

VARCHAR

DATE

LONG VARCHAR

DATE

DATE

DATE (no conversion)

Type conversion for TI comparison

Source type

Converted type

CHAR

TIME

VARCHAR

TIME

LONG VARCHAR

TIME

TIME

TIME (no conversion)

Type conversion for TZ comparison

Source type

Converted type

CHAR

TIME_TZ

VARCHAR

TIME_TZ

LONG VARCHAR

TIME_TZ

TIME

TIME_TZ

TIME_TZ

TIME_TZ (no conversion)

Type conversion for TS comparison

Source type

Converted type

CHAR

TIMESTAMP

VARCHAR

TIMESTAMP

LONG VARCHAR

TIMESTAMP

DATE

DATE (no conversion)

TIMESTAMP

TIMESTAMP (no conversion)

Type conversion for SZ comparison

Source type

Converted type

CHAR

TIMESTAMP_TZ

VARCHAR

TIMESTAMP_TZ

LONG VARCHAR

TIMESTAMP_TZ

DATE

TIMESTAMP_TZ

TIMESTAMP

TIMESTAMP_TZ

TIMESTAMP_TZ

TIMESTAMP_TZ (no conversion)

Type conversion for YM comparison

Source type

Converted type

CHAR

INTERVAL_YM

VARCHAR

INTERVAL_YM

LONG VARCHAR

INTERVAL_YM

NATIVE_SMALLINT

INTERVAL_YM

NATIVE_INTEGER

INTERVAL_YM

NATIVE_BIGINT

INTERVAL_YM

NUMBER

INTERVAL_YM

NUMERIC

INTERVAL_YM

FLOAT

INTERVAL_YM

INTERVAL_YM

INTERVAL_YM (no conversion)

Type conversion for DS comparison

Source type

Converted type

CHAR

INTERVAL_DS

VARCHAR

INTERVAL_DS

LONG VARCHAR

INTERVAL_DS

NATIVE_SMALLINT

INTERVAL_DS

NATIVE_INTEGER

INTERVAL_DS

NATIVE_BIGINT

INTERVAL_DS

NUMBER

INTERVAL_DS

NUMERIC

INTERVAL_DS

FLOAT

INTERVAL_DS

INTERVAL_DS

INTERVAL_DS (no conversion)

Type conversion for BO comparison

Source type

Converted type

CHAR

BOOLEAN

VARCHAR

BOOLEAN

LONG VARCHAR

BOOLEAN

BOOLEAN

BOOLEAN (no conversion)

Type conversion for RI comparison

Source type

Converted type

CHAR

ROWID

VARCHAR

ROWID

LONG VARCHAR

ROWID

ROWID

ROWID (no conversion)

Type Conversion

Type conversions are classified into implicit and explicit type conversions.

The availability of type conversion refers to the ability to convert a data type from one type  to another.

In the type conversion table, built-in data types are represented by abbreviated strings enclosed in double quotes ("").

The availability of type conversion

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

VARCHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

LONG VARCHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

BINARY

O

O

O

VARBINARY

O

O

O

LONG VARBINARY

O

O

O

NATIVE_SMALLINT

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_INTEGER

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_BIGINT

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_REAL

O

O

O

O

O

O

O

O

O

O

O

NATIVE_DOUBLE

O

O

O

O

O

O

O

O

O

O

O

NUMBER

O

O

O

O

O

O

O

O

O

O

O

O

O

NUMERIC

O

O

O

O

O

O

O

O

O

O

O

O

O

FLOAT

O

O

O

O

O

O

O

O

O

O

O

O

O

DATE

O

O

O

O

O

O

TIME

O

O

O

O

O

TIME_TZ

O

O

O

O

O

TIMESTAMP

O

O

O

O

O

O

O

TIMESTAMP_TZ

O

O

O

O

O

O

O

O

INTERVAL_YM

O

O

O

O

O

O

O

O

O

O

INTERVAL_DS

O

O

O

O

O

O

O

O

O

O

BOOLEAN

O

O

O

O

ROWID

O

O

O

O

Type Combination

When Type Combination Is Required

The CASE operator and set operator produce multiple expressions as the result of the operation.
When, as in the example below, each expression has a different type, the result type must be determined.
SELECT CASE expr WHEN expr THEN char(3)
                 WHEN expr THEN char(5)
                 ELSE char(1)
       END   
  FROM t1;
SELECT float_column
  FROM t1
UNION ALL
SELECT number_precision_column
  FROM t2
UNION ALL
SELECT native_integer_column
  FROM t3;

A rule is applied to determine the result type based on the combination of types. The following is an example of how the rule is applied.

Result Type Combination Rule

Each expression's data type must belong to the same family of types that can be combined.

The result types determined by the result type combination rule are shown in the table below.

The following abbreviations are used to describe the result type combination rules.

In the result type combination table, built-in data types are represented by abbreviated words enclosed in double quotes ("").

The result type determined by the result type combination rule

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

VC

VC

VARCHAR

VC

VC

LONG VARCHAR

LC

BINARY

VB

VB

VARBINARY

VB

VB

LONG VARBINARY

LB

NATIVE_SMALLINT

NS

NI

NB

ND

ND

NU

NU

NU

NATIVE_INTEGER

NI

NI

NB

ND

ND

NU

NU

NU

NATIVE_BIGINT

NB

NB

NB

ND

ND

NU

NU

NU

NATIVE_REAL

ND

ND

ND

NR

ND

ND

ND

ND

NATIVE_DOUBLE

ND

ND

ND

ND

ND

ND

ND

ND

NUMBER

NU

NU

NU

ND

ND

NU

NU

NU

NUMERIC

NU

NU

NU

ND

ND

NU

NU

NU

FLOAT

NU

NU

NU

ND

ND

NU

NU

FL

DATE

DA

TS

SZ

TIME

TI

TZ

TIME_TZ

TZ

TZ

TIMESTAMP

TS

TS

SZ

TIMESTAMP_TZ

SZ

SZ

SZ

INTERVAL_YM

YM

INTERVAL_DS

DS

BOOLEAN

BO

ROWID

RI

Compatibility for Data Type

The SQL standard compatibility for data type is as follows.

SQL standard compatibility for data types

Feature ID

Description

Availability

B033

Untyped SQL-invoked function arguments

X

E011-01

INTEGER and SMALLINT data types

O

E011-02

REAL, DOUBLE PRECISION, and FLOAT data types

O

E011-03

DECIMAL and NUMERIC data types

X

E011-04

Arithmetic operators

O

E011-05

Numeric comparison

O

E011-06

Implicit casting among the numeric data types

O

E021-01

CHARACTER data type

O

E021-02

CHARACTER VARYING data type

O

E021-03

Character literals

O

E021-04

CHARACTER_LENGTH function

O

E021-05

OCTET_LENGTH function

O

E021-06

SUBSTRING function

O

E021-07

Character concatenation

O

E021-08

UPPER and LOWER functions

O

E021-09

TRIM function

O

E021-10

Implicit casting among the fixed-length and variable-length character string types

O

E021-11

POSITION function

O

E021-12

Character comparison

O

E071-05

Columns combined via table operators need not have exactly the same data type

O

F051-01

DATE data type (including support of DATE literal)

O

F051-02

TIME data type (including support of TIME literal) with fractional seconds precision of at least 0

O

F051-03

TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6

O

F051-04

Comparison predicate on DATE, TIME, and TIMESTAMP data types

X

F051-05

Explicit CAST between datetime types and character string types

O

F054

TIMESTAMP in DATE type precedence list

X

F382

Alter column data type

O

F611

Indicator data types

X

F741

Referential MATCH types

X

J521

JDBC data types

X

J622

external Java types

X

S011-01

USER_DEFINED_TYPES view

X

S023

Basic structured types

X

S024

Enhanced structured types

X

S025

Final structured types

X

S026

Self-referencing structured types

X

S041

Basic reference types

X

S043

Enhanced reference types

X

S051

Create table of type

X

S071

SQL paths in function and type name resolution

X

S091-01

Arrays of built-in data types

X

S091-02

Arrays of distinct types

X

S092

Arrays of user-defined types

X

S094

Arrays of reference types

X

S161

Subtype treatment

X

S162

Subtype treatment for references

X

S201-02

Array as result type of functions

X

S231

Structured type locators

X

S261

Specific type method

X

S272

Multisets of user-defined types

X

S274

Multisets of reference types

X

S281

Nested collection types

X

S401

Distinct types based on array types

X

S402

Distinct types based on distinct types

X

T021

BINARY and VARBINARY data types

O

T022

Advanced support for BINARY and VARBINARY data types

O

T031

BOOLEAN data type

O

T041

Basic LOB data type support

X

T042

Extended LOB data type support

X

T051

Row types

X

T071

BIGINT data type

O

T201

Comparable data types for referential constraints

X

T322

Declared data type attributes

X

X010

XML type

X

X011

Arrays of XML type

X

X012

XMultisets of XML type

X

X013

Distinct types of XML type

X

X014

Attributes of XML type

X

X015

Fields of XML type

X

X181

XML(DOCUMENT(UNTYPED)) type

X

X182

XML(DOCUMENT(ANY)) type

X

X190

XML(SEQUENCE) type

X

X191

XML(DOCUMENT(XMLSCHEMA)) type

X

X192

XML(CONTENT(XMLSCHEMA)) type

X

X231

XML(CONTENT(UNTYPED)) type

X

X232

XML(CONTENT(ANY)) type

X

X251

Persistent XML values of XML(DOCUMENT(UNTYPED)) type

X

X252

Persistent XML values of XML(DOCUMENT(ANY)) type

X

X253

Persistent XML values of XML(CONTENT(UNTYPED)) type

X

X254

Persistent XML values of XML(CONTENT(ANY)) type

X

X255

Persistent XML values of XML(SEQUENCE) type

X

X256

Persistent XML values of XML(DOCUMENT(XMLSCHEMA)) type

X

X257

Persistent XML values of XML(CONTENT(XMLSCHEMA)) type

X

X260

XML type: ELEMENT clause

X

X261

XML type: NAMESPACE without ELEMENT clause

X

X263

XML type: NO NAMESPACE with ELEMENT clause

X

X264

XML type: schema location

X

X410

Alter column data type: XML type

X

Format String

A format string defines the format used when a numeric type or date/time type is converted to a character string, or when a character string is converted to a numeric type or date/time type.
Format strings are classified by type.
• Numeric data type: Refer to Number Format String.
• Date/time type: Refer to Datetime Format String.

Number Format String

The number format string defines the format used when a numeric type is converted to a character string, or when a character string is converted to a numeric type.
The number format string is used as an argument in functions such as TO_CHAR( number ), TO_NATIVE_SMALLINT, TO_NATIVE_INTEGER, TO_NATIVE_BIGINT, TO_NUMBER, TO_NATIVE_REAL, TO_NATIVE_DOUBLE.
The number format string can specify multiple format elements based on the desired format.
All number format elements are rounded to fit the specified format.
If the number of digits before the decimal point in the value to be converted exceeds the number of digits specified in the format string, the extra digits are replaced with the # character.
If the format element representing the sign of MI, S, PR is not specified, a negative number will display a - sign, and a positive number will have a space in front.
Number format elements

Format

element

Example

Description

, (comma)

9,999

It returns a comma to the specified position.

Multiple commas can be specified.

The format string can not begin with a comma, nor can a comma appear after the decimal point (.).

. (period)

99.99

It returns a decimal point (.) to the specified position.

The decimal point in the format string can only be specified once.

$

$9999

It returns the $ sign to the front of the number.

0

0999

9990

It returns a zero (0) to the front or the end of the number.

If the number of digits in the value to be converted is smaller than the number of digits to the zero position in the format string, the gap is filled with zero (0)s and returned.

9

9999

It returns a white space and numbers according to the sign and the number of specified 9.

If the number of digits in the value to be converted is smaller than the number of specified 9, the gap is filled with white spaces and returned.

For a positive number, a white space is returned to the front of the number. For a negative number, a '-' symbol is returned to the front of the number.

If the value before the decimal point in the format string is 0, a white space is returned instead of 0.

e.g. TO_CHAR( 0.123, '9.999' ) → .123

e.g. TO_CHAR( 0, '9' ) → 0

B

B9999

If the value is zero, a white space is returned.

EEEE

9.9EEEE

It returns the value in exponential notation.

It can appear at the end of the format string or in front of S, MI, or PR.

It can not be specified together with a comma (,).

MI

9999MI

For a positive number, a white space is returned to the end of the number. For a negative number, a '-' symbol is returned to the end of the number.

It can only be specified at the end of the format string and can not be used together with S or PR.

PR

9999PR

For a positive number, white spaces are returned to both the beginning and end of the number.

For a negative number, it returns the number enclosed within angle brackets. <number>

It can only be specified at the end of the format string, and can not be used together with S or MI.

RN

rn

RN

rn

Roman numerals are converted to uppercase and returned. (RN)

Roman numerals are converted to lowercase and returned. (rn)

Only numbers between 1 ~ 3999 are supported.

It can only be used together with the FM format element and can not be combined with any other format elements.

It can not be used in the TO_NUMBER function.

S

S9999

9999S

For a positive number, a '+' symbol is returned to the front of the number. For a negative number, a '-' symbol is returned to the front of the number. (S9999)

For a positive number, a '+' symbol is returned to the end of the number. For a negative number, a '-' symbol is returned to the end of the number. (9999S)

It can only be specified at the beginning or at the end of the format string.

It can not be used together with MI or PR.

V

999V99

When the number of digits of 9 following the V format element is n, the value is multiplied by 10n.

It can not be specified together with a decimal point (.).

It can not be used in the TO_NUMBER function.

X

XXXX

xxxx

It returns a white space and a hexadecimal number based on the specified number of X digits.

The integer value is converted to a hexadecimal number and returned. (Non-integer values are rounded to the nearest integer.)

XXX returns hexadecimal digits in uppercase letters, while xxxx returns them in lowercase letters.

If the number of converted hexadecimal digits is smaller than the number specified by X, the gap is filled with white spaces.

Only 0 and positive integers are processed, and negative numbers are replaced with '#'.

It can only be used with the 0 and FM format elements, but can not be combined with any other format elements.

FM

FM

It removes the leading and trailing white spaces, and returns a left-aligned effect.

It removes the leading and trailing white spaces from the number.

It removes the zeros (0) added after the decimal point by the 9 format element.

Following are examples of using number format strings.

TO_CHAR( 12345, '99,999' )           : ' 12,345'
TO_CHAR( 123456789, '999,999,999' )  : ' 123,456,789'
TO_CHAR( 12.345, '99.999' )          : ' 12.345'
TO_CHAR( 1234.56, '$9,999.99' )      : ' $1,234.56'
TO_CHAR( 123, '099999' )             : ' 000123'
TO_CHAR( 0.2, '0.9' )                : ' 0.2'
TO_CHAR( 123.45, '999999.99' )       : '    123.45'
TO_CHAR( -123.45, '999999.99' )      : '   -123.45'
TO_CHAR( 123.45, 'FM999999.99' )     : '123.45'
TO_CHAR( -123.45, 'FM999999.99' )    : '-123.45'
TO_CHAR( 12345.67, '999.99' )        : '#######'
TO_CHAR( 123.100567, '999.999' )     : ' 123.101'
TO_CHAR( 0.2, '90.99' )              : '  0.20'
TO_CHAR( 0.2, '99.99' )              : '   .20'
TO_CHAR( 0, '90.99' )                : '  0.00'
TO_CHAR( 0, 'B90.99' )               : '      '
TO_CHAR( 123.45, '9.9EEEE' )         : '  1.2E+02'
TO_CHAR( 123.45, '999.99MI' )        : '123.45 '
TO_CHAR( -123.45, '999.99MI' )       : '123.45-'
TO_CHAR( 123.45, '999.99PR' )        : ' 123.45 '
TO_CHAR( -123.45, '999.99PR' )       : '<123.45>'
TO_CHAR( 123, 'RN' )                 : '         CXXIII'
TO_CHAR( 123, 'rn' )                 : '         cxxiii'
TO_CHAR( 123, 'FMRN' )               : 'CXXIII'
TO_CHAR( 4000, 'RN' )                : '###############'
TO_CHAR( 123.45, 'S999.99' )         : '+123.45'
TO_CHAR( -123.45, 'S999.99' )        : '-123.45'
TO_CHAR( 123.45, '999.99S' )         : '123.45+'
TO_CHAR( -123.45, '999.99S' )        : '123.45-'
TO_CHAR( 123.45, '999V999' )         : ' 123450'
TO_CHAR( 123, 'XX' )                 : ' 7B'
TO_CHAR( 123, 'xx' )                 : ' 7b'
TO_CHAR( 45678, 'XXXXXXX' )          : '    B26E'
TO_CHAR( 45678, 'FMXXXXXXX' )        : 'B26E'
TO_CHAR( 123.45, '99,999.999999' )   : '    123.450000'
TO_CHAR( 123.45, 'FM99,999.999999' ) : '123.45'

Datetime Format String

The datetime format string is a string that defines the format used to convert a date/time value to a string, or to convert a string to a date/time value.
A datetime format string is used as an argument in functions such as TO_CHAR( datetime ), TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_WITH_TIME_ZONE, and TO_TIME, TO_TIME_WITH_TIME_ZONE.
For a datetime format string, if no format is specified, the default value is used. The default value for each type is defined in the session property (NLS _ * _ FORMAT).
NLS * _FORMAT values can be modified using the ALTER SESSION SET property_name.
In a datetime format string, multiple format elements can be specified to achieve the desired representation.
Datetime format elements

Format

element

Whether to use TO_*

datetime

Description

-

/

,

.

;

:

"text"

Special characters

Y

It returns the character of the format element to the specified location.

AD

A.D.

Y

AD, with or without periods.

AM

A.M.

Y

AM, with or without periods.

BC

B.C.

Y

BC, with or without periods.

CC

N

Century

If the last two digits of a four-digit year are between 01 and 99, the value obtained by adding one to the first two digits is returned. (e.g. If the year is 2005, 21 is returned.)

If the last two digits of the four-digit year are 00, the first two digits are returned. (e.g. If the year is 2000, 20 is returned.)

D

Y

It returns the day number of the week (1 to 7).

Sunday is 1, Saturday is 7, and so on.

DAY

Day

day

Y

It returns the day of the week. (e.g. SUNDAY )

  • DAY: It returns the day in uppercase.

  • Day: It returns the day with the first letter capitalized and the rest in lowercase.

  • day: It returns the day in lowercase.

DD

Y

It returns the day of the month (1 to 31).

DDD

Y

It returns the day of the year (1 to 366).

DY

Dy

dy

Y

It returns the abbreviated form of the day of the week. (e.g. SUN)

  • DY: It returns the day in uppercase.

  • Dy: It returns the day with the first letter capitalized and the rest in lowercase.

  • dy: It returns the day in lowercase.

FF[1..6]

Y

It returns fractional seconds with the number of digits specified after "FF" (1 to 6).

If no number is specified, the default value is 6 (i.e., "FF" is equivalent to "FF6").

If the number of fractional second digits exceeds the number specified after FF, they are rounded down.

If the number of fractional second digits is fewer than the number specified after FF, zeros (0) are added to match the specified number.

It can not be used with the DATE type.

HH

HH12

Y

The hour (1 ~ 12)

HH24

Y

The hour (0 ~ 23)

IW

N

The week containing the first Thursday of the year is designated as the first calendar week according to the ISO 8601 standard (week 1 to 52 or 1 to 53).

  • The calendar week starts on Monday.

  • The first calendar week always include January 4th.

  • The first calendar week may also include December 29th, 30th, and 31st.

  • The last calendar week may include January 1st, 2nd, and 3rd.

IYYY

N

The 4-digit year that contains the calendar week defined by the ISO 8601 standard.

IYY

IY

I

N

The 3-digit year that contains the calendar week defined by the ISO 8601 standard

The 2-digit year that contains the calendar week defined by the ISO 8601 standard

The single digit year that contains the calendar week defined by the ISO 8601 standard

J

Y

Julian day: The number of days since November 24, 4714 BC

MI

Y

Minute (0 ~ 59)

MM

Y

Month (01 ~ 12), January (01) ~ December (12)

MON

Mon

mon

Y

The abbreviated form of the month (e.g. JAN)

  • MON: It returns the month in uppercase.

  • Mon: It returns the month with the first letter capitalized and the rest in lowercase.

  • mon: It returns the month in lowercase.

MONTH

Month

month

Y

The month name (e.g. JANUARY )

  • MONTH: It returns the month name in uppercase.

  • Month: It returns the month name with the first letter capitalized and the rest in lowercase.

  • month: It returns the month name in lowercase.

PM

P.M.

Y

PM, with or without periods.

Q

N

The quarter of the year (1 ~ 4)

January to March is 1 and October to December is 4.

RM

Rm

rm

Y

It returns the Roman numeral for the month. (e.g. I)

  • RM: It returns the Roman numeral in uppercase.

  • Rm: It returns the Roman numeral with the first letter capitalized and the rest in lowercase.

  • rm: It returns the Roman numeral in lowercase.

RR

Y

Adjusted two-digit year

The two-digit year represented by RR can be converted to a four-digit year as follows.

  • When the two-digit year (RR) is between 00 and 49:

    • If the last two digits of the current year are between 00 and 50,

      • the four-digit year is formed using the first two digits of the current year and the two digits represented by RR.

    • If the last two digits of the current year are between 51 and 99,

      • the four-digit year is formed using the first two digits of the current year+1, and the two digits represented by RR.

  • When the two-digit year (RR) is between 50 and 99:

    • If the last two digits of the current year are between 00 and 50,

      • the four-digit year is formed using the first two digits of the current year - 1, and the two digits represented by RR.

    • If the last two digits of the current year are between 51 and 99,

      • the four-digit year is formed using the first two digits of the current year and the two digits represented by RR.

RRRR

Y

Adjusted four-digit year

Both two-digit and four-digit years can be input.

Two-digit input is processed the same way as RR

SS

Y

Second (0 ~ 59)

SSSSS

Y

Seconds since the last midnight (0 ~ 86399)

TZH

Y

Time Zone Hour

This can not be used with DATE, TIMESTAMP, or TIME types. It is available for use with TIMESTAMP WITH TIME ZONE, and TIME WITH TIME ZONE types.

TZM

Y

Time Zone Minute

This can not be used with DATE, TIMESTAMP, or TIME types. It can only be used with TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE types.

WW

N

The sequence of the week in a year. (1~ 53)

Week 1 starts on the first day of the year and continues through to the seventh day of the year.

W

N

The sequence of the week in a month. (1 ~ 5)

Week 1 starts on the first day of the month and ends on the seventh day.

Y,YYY

Y

It returns the year in the "Y,YYY" format, with a comma separating the thousands.

YYYY

SYYYY

Y

Four-digit year.

SYYYY displays the sign of the year.

  • If the year is BC, it displays a '-'. If the year is AD, it displays a ' '.

YYY

YY

Y

Y

  • YYY: The last three digits of the current year

  • YY: The last two digits of the current year

  • Y: The last digit of the current year

The following are examples of using a datetime format string.

* - / , . ; : "text" Special character 
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30', 'YYYY-MM-DD HH12:MI:SS' ),
             'YYYY/MM/DD HH12:MI:SS' )
    ==> '2012/07/15 03:30:30'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 
             'YYYY"year" DDD"th day"' )
    ==> '2012year 197th day'
* AD
  • TO_CHAR( TO_DATE( '2012-07-15 AD', 'YYYY-MM-DD AD' ), 'YYYY AD' )
    ==> '2012 AD'
  • TO_CHAR( TO_DATE( '0001-01-01 BC', 'YYYY-MM-DD AD'), 'YYYY AD' )
    ==> '0001 BC'

* BC 
  • TO_CHAR( TO_DATE( '0001-01-01 BC', 'YYYY-MM-DD BC'), 'YYYY BC' )
    ==> '0001 BC'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'YYYY BC' )
    ==> '2012 AD'
* AM
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30 AM',
                      'YYYY-MM-DD HH12:MI:SS AM' ),
             'HH12:MI:SS AM' )
    ==> '03:30:30 AM'
  • TO_CHAR( TO_DATE( '2012-07-15 21:30:30', 'YYYY-MM-DD HH24:MI:SS' ),
             'HH12:MI:SS AM' )
    ==> '09:30:30 PM'

* PM 
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30', 
                      'YYYY-MM-DD HH24:MI:SS' ), 
             'HH12:MI:SS PM' )
    ==> '03:30:30 AM'
  • TO_CHAR( TO_DATE( '2012-07-15 09:30:30 PM', 
                      'YYYY-MM-DD HH12:MI:SS PM' ), 
             'HH12:MI:SS PM' )
    ==> '09:30:30 PM'
* CC
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'CC' )
    ==> '21'
* D
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'D' )
    ==> '1'

* DD
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DD' )
    ==>  '15'

* DDD
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DDD' )
    ==> '197'
* DAY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DAY' )
    ==> 'SUNDAY   '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Day' )
    ==> 'Sunday   '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'day' )
    ==> 'sunday   '

* DY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DY' )
    ==> 'SUN'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Dy' )
    ==> 'Sun'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'dy' )
    ==> 'sun'
* FF[1 ... 6]
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'FF' )
    ==> '123456'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'FF5' )
    ==> '12345'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.9',
                           'YYYY-MM-DD HH24:MI.SS.FF1' ) , 
             'FF6' )
    ==> '900000'
* HH HH12 HH24
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH12' )
    ==> '03'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH12' )
    ==> '11'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH24' )
    ==> '23'
* IW
  • TO_CHAR( DATE'2016-01-01', 'IW' )
    ==> 53
  • TO_CHAR( DATE'2014-12-30', 'IW' )
    ==> 01
* IYYY
  • TO_CHAR( DATE'2016-01-01', 'IYYY' )
    ==> 2015
  • TO_CHAR( DATE'2014-12-30', 'IYYY' )
    ==> 2015

* IYY
  • TO_CHAR( DATE'2016-01-01', 'IYY' )
    ==> 015

* IY
  • TO_CHAR( DATE'2016-01-01', 'IY' )
    ==> 15

* I
  • TO_CHAR( DATE'2016-01-01', 'I' )
    ==> 5
* J
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'J' )
    ==> '2456124'
  • TO_CHAR( TO_DATE( '2456124', 'J' ), 'YYYY-MM-DD' )
    ==> '2012-07-15'
* MI
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
             'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'MI' ) 
    ==> '30'
* MM
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'MM' )
    ==> '07'
* MON
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'MON' )
    ==> 'JUL'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Mon' )
    ==> 'Jul'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'mon' )
    ==> 'jul'

* MONTH
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'MONTH' )
    ==> 'JULY     '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Month' )
    ==> 'July     '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'month' )
    ==> 'july     '
* Q
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Q' )
    ==> '3'
* RM
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'RM' )
    ==> 'VII ' 
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Rm' )
    ==> 'Vii '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'rm' )
    ==> 'vii '
* RR, RRRR ( The current year is 2014. )
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '1950'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '1950'
  • TO_CHAR( TO_DATE( '50-07-15', 'YY-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '49-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '50-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '1950'

* RR, RRRR ( The current year is 2051. )
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '50-07-15', 'YY-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '49-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '50-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2050'
* SS
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456',
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'SS' )
    ==> '45' 

* SSSSS
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456',
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'SSSSS' )
    ==> '84645'
* TZH 
  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZH' )
    ==> '+09'

* TZM
  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZM' )
    ==> '00'

  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZH:TZM' )
    ==> '+09:00'
* WW
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'WW' )
    ==> '29'

* W
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'W' )
    ==> '3'
* Y,YYY
  • TO_CHAR( TO_DATE( '2,012-07-15', 'Y,YYY-MM-DD' ), 'Y,YYY' )
    ==> '2,012'

* YYYY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'YYYY' )
    ==> '2012'

* SYYYY
  • TO_CHAR( TO_DATE( '-0001-01-01', 'SYYYY-MM-DD' ), 'SYYYY' )
    ==> '-0001' 
  • TO_CHAR( TO_DATE( '2000-01-01', 'SYYYY-MM-DD' ), 'SYYYY' )
    ==> ' 2000'

* YYY
  • TO_CHAR( TO_DATE( '012-07-15', 'YYY-MM-DD' ), 'YYY' )
    ==> '012'
  • TO_CHAR( TO_DATE( '012-07-15', 'YYY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 1000) is 2 )

* YY
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YY' )
    ==> '12'
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 100) is 20 ) 
    ==> '2112' ( (Current year / 100) is 21 ) 

* Y
  • TO_CHAR( TO_DATE( '2-07-15', 'Y-MM-DD' ), 'Y' )
    ==> '2'
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 10 years) is 201 )
    ==> '2052' ( (Current year / 10 years) is 205 )

Expressions

An expression is a combination of values, operators, and functions used to retrieve data.
The following shows the positions in SQL where expressions can be used.
• Target clause in SELECT
• GROUP BY clause in SELECT
• ORDER BY clause in SELECT
• WHERE clause and HAVING clause in SELECT
• INSERT VALUES clause
• UPDATE SET clause
• RETURN clause in INSERT, DELETE, UPDATE
Expression types are as follows.
• Simple expression
• Compound expression
• Boolean value expression
• Case expression
• Datetime expression
• Scalar subquery expression
• Sequence manipulation expression
Simple expressions include columns, pseudo-columns, literals, and null values.
Compound expressions are combinations of multiple expressions.
For more information, refer to the following.
•  Null Value
•  Literals
•  Pseudo Columns
•  Operators
•  Functions

Boolean Value Expression

Syntax

<boolean value expression> ::=
        <boolean term>
      | <boolean value expression> OR <boolean term>

<boolean term> ::=
        <boolean factor>
      | <boolean term> AND <boolean factor>

<boolean factor> ::=
        [ NOT ] <boolean test>

<boolean test> ::=
        <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::=
        TRUE
      | FALSE
      | UNKNOWN

<boolean primary> ::=
        <column>
        <condition>
      | <boolean predicand>

<boolean predicand> ::=
        <parenthesized boolean value expression>
      | <nonparenthesized value expression primary>

<parenthesized boolean value expression> ::=
        <left paren> <boolean value expression> <right paren>

Description

A <boolean value expression> describes a boolean value. <boolean primary> expressions that have a boolean value include <column>, <condition>, and <boolean predicand>. A <column> must be declared as the BOOLEAN type, but it can also return a boolean value using CAST.
A <boolean value expression> can be used with logical operators such as AND, OR, and NOT, and supports boolean-specific operators like IS and IS NOT.
The IS and IS NOT operators, as described in <boolean test>, determine whether the boolean value in <boolean primary> matches one of the <truth value> (TRUE, FALSE, UNKNOWN).
For more information, refer to Conditions.

Example

gSQL> SELECT * FROM T1 WHERE CAST('TRUE' AS BOOLEAN);

I1   
-----
TRUE 
FALSE
null 

3 rows selected.

gSQL> SELECT * FROM T1 WHERE I1;

I1  
----
TRUE

1 row selected.

gSQL> SELECT * FROM T1 WHERE I1 IS TRUE;

I1  
----
TRUE

1 row selected.

gSQL> SELECT * FROM T1 WHERE I1 IS NOT FALSE;

I1  
----
TRUE
null

2 rows selected.

gSQL> SELECT * FROM T1 WHERE I1 IS UNKNOWN;

I1  
----
null

1 row selected.

CASE Expression

Syntax

<case expression> ::=
        <simple case>
      | <searched case>

<simple case> ::=
        CASE expr WHEN comparison_expr THEN result 
                 [ WHEN comparison_expr THEN result ... ] 
                 [ ELSE result ]
        END

<searched case> ::=
        CASE WHEN condition THEN result
             [ WHEN condition THEN result ... ] 
             [ ELSE result ]
        END

Description

The WHEN ... THEN clauses are evaluated in the order they appear in the CASE statement.
If a comparison result is FALSE, the subsequent WHEN ... THEN clauses are evaluated until TRUE is encountered.
If a comparison result is TRUE, the corresponding result is returned, and no further evaluations are performed.
• Simple case 
  The comparison_expr in the CASE expr and the WHEN ... THEN clause is evaluated using the equal operation.   
  (expr = comparison_expr).
• Searched case
  The condition in the WHEN ... THEN clause is evaluated.
If all evaluations in the WHEN clauses result in FALSE, the result of the ELSE clause is returned.
If the ELSE clause is omitted, NULL is returned.

If there are multiple types of results in the THEN or ELSE clauses, the result type is determined by the Result Type Combination Rule.

For more information, refer to the following.
• COALESCE
• NULLIF

Example

gSQL> SELECT I1,
             CASE I1 WHEN 1 THEN 'ONE'
                     WHEN 2 THEN 'TWO'
                     ELSE 'NUMBER'
             END AS CASE_RESULT1,
             CASE I1 WHEN 1 THEN 'ONE'
                     WHEN 2 THEN 'TWO'
             END AS CASE_RESULT2
        FROM T1;
I1 CASE_RESULT1 CASE_RESULT2
-- ------------ -----------
 1 ONE          ONE        
 2 TWO          TWO        
 3 NUMBER       null       
3 rows selected.
gSQL> SELECT I1,
             CASE WHEN I1 = 1 THEN 'ONE'
                  WHEN I1 = 2 THEN 'TWO'
                  ELSE 'NUMBER'    
             END AS CASE_RESULT1,
             CASE WHEN I1 = 1 THEN 'ONE'
                  WHEN I1 = 2 THEN 'TWO'
             END AS CASE_RESULT2 
        FROM T1;
I1 CASE_RESULT1 CASE_RESULT2
-- ------------ ------------
 1 ONE          ONE         
 2 TWO          TWO         
 3 NUMBER       null        
3 rows selected.

CAST Specification

Syntax

CAST( expression AS data_type )

Description

CAST converts the data type of an expression to the specified data_type.

Example

gSQL> SELECT CAST( '1-2' AS INTERVAL YEAR TO MONTH ) AS RESULT FROM DUAL;  
RESULT
------
+01-02
1 row selected.

Scalar Subquery Expression

A scalar subquery expression is a subquery that returns a single row with one column as a result. The result of the scalar subquery expression is the value(s) specified in the subquery's select list.
If the subquery does not return any rows, the result is NULL, and if it returns two or more rows, an error occurs.
A scalar subquery expression can be described in most positions where an expression is allowed, but the subquery must be enclosed in parentheses. Even when a scalar subquery expression is used as an argument in a function and is already enclosed in parentheses, it must be enclosed in separate parentheses for the subquery itself. Otherwise, an error will occur.
The following is an example of using a scalar subquery expression.
gSQL> select * from dual where dummy = (select * from dual);

DUMMY
-----
X    

1 row selected.

gSQL> select sum(select 1 from dual) from dual;

ERR-42000(40000): syntax error 
select sum(select 1 from dual) from dual
...........^    ^
Error at line 1

gSQL> select sum((select 1 from dual)) from dual;

SUM((SELECT 1 FROM DUAL))
-------------------------
                        1

1 row selected.

Compatibility

The SQL standard compatibility for expressions is as follows.

SQL standard compatibility for expressions

Feature ID

Description

Availability

E121-03

Value expressions in ORDER BY clause

O

F051-05

Basic date and time Explicit CAST between datetime types and character string types

O

F201

CAST function

O

F261-01

Simple CASE

O

F261-02

Searched CASE

O

F261-03

NULLIF

O

F261-04

COALESCE

O

F263

Comma-separated predicates in simple CASE expression

X

F301

CORRESPONDING in query expressions

X

F385

Drop column generation expression clause

X

F561

Full value expressions

X

F846

Octet support in regular expression operators

X

F847

Nonconstant regular expressions

X

F850

Top-level <order by clause> in <query expression>

O

F855

Nested <order by clause> in <query expression>

O

F856

Nested <fetch first clause> in <query expression>

O

F857

Top-level <fetch first clause> in <query expression>

O

F861

Top-level <result offset clause> in <query expression>

O

F863

Nested <result offset clause> in <query expression>

O

S091-03

Arrays expressions

X

S111

ONLY in query expressions

X

T121

WITH (excluding RECURSIVE) in query expression

O

T581

Regular expression substring function

X

Pseudo Columns

A pseudo column is similar not only to a function, but also to a table column, as it can return a different value for each row every time it is executed.
Supported pseudo column

Name

Description

Refer to

CURRVAL

It is a pseudo column associated with a sequence.

CURRVAL

NEXTVAL

It is a pseudo column associated with a sequence.

ROWID Pseudo Column

ROWNUM

It is the row number that satisfies the condition.

ROWNUM

ROWID

It returns the record identifier in the database.

ROWID Pseudo Column

CLUSTER_GROUP_ID

It returns the group identifier in the database.

CLUSTER_GROUP_ID Pseudo Column

CLUSTER_MEMBER_ID

It returns the member identifier where the record is stored.

CLUSTER_MEMBER_ID Pseudo Column

CLUSTER_GROUP_NAME

It returns the group name where the record is stored.

CLUSTER_GROUP_NAME Pseudo Column

CLUSTER_MEMBER_NAME

It returns the member name where the record is stored.

CLUSTER_MEMBER_NAME Pseudo Column

CLUSTER_SHARD_ID

It returns the shard identifier where the record is stored.

CLUSTER_SHARD_ID Pseudo Column

ROWID Pseudo Column

The ROWID pseudo column is a record identifier that returns the identification information for each database record.

ROWID contains the information needed to identify the location within the database, depending on the system.

Standalone system
• OBJECT_ID
• TABLESPACE_ID
• PAGE_ID
• OFFSET in PAGE
Cluster system
• GRID_BLOCK_SEQUENCE
• GRID_BLOCK_ID
• MEMBER_ID
• SHARD_ID
When querying ROWID, the information stored internally in base 64 encoding is converted into values such as A-Z, a-z, 0-9, +, and / for output.
Each piece of information used to identify the address within the database, stored in ROWID, can be obtained using ROWID-related functions.
The address of a deleted record can be reassigned to a record that is to be inserted.
The ROWID pseudo column can only be used in SELECT operations and can not be used in INSERT, UPDATE, or DELETE operations.
For more information, refer to ROWID, ROWID-related Functions.
The following is an example of querying the ROWID pseudo column.
gSQL> SELECT ROWID FROM T1;
                  ROWID
-----------------------
AAAAAAAAFpEAACAAAEAkAAA
AAAAAAAAFpEAACAAAEAkAAB
AAAAAAAAFpEAACAAAEAkAAC
AAAAAAAAFpEAACAAAEAkAAD
AAAAAAAAFpEAACAAAEAkAAE
5 rows selected.

CLUSTER_GROUP_ID Pseudo Column

The CLUSTER_GROUP_ID pseudo column returns the group identifier of the server where the record is stored.

The CLUSTER_GROUP_ID pseudo column can be used in SELECT operations, but can not be used in INSERT, UPDATE, or DELETE operations.

This information is valid in the cluster system.

The following is an example of retrieving the CLUSTER_GROUP_ID pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_GROUP_ID FROM T1;
C1 T1.CLUSTER_GROUP_ID
-- -------------------
A                    1
B                    2
C                    3

3 rows selected.

CLUSTER_MEMBER_ID Pseudo Column

The CLUSTER_MEMBER_ID pseudo column returns the member identifier of the server where the record is stored.

The CLUSTER_MEMBER_ID pseudo column can be used in SELECT operations, but can not be used in INSERT, UPDATE, or DELETE operations.

This information is valid in the cluster system.

The following is an example of retrieving the CLUSTER_MEMBER_ID pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_MEMBER_ID FROM T1;
C1 T1.CLUSTER_MEMBER_ID
-- --------------------
A                     1
B                     3
C                     5

3 rows selected.

CLUSTER_GROUP_NAME Pseudo Column

The CLUSTER_GROUP_NAME pseudo column returns the group name of the server where the record is stored.

The CLUSTER_GROUP_NAME pseudo column can be used in SELECT operations, but can not be used in INSERT, UPDATE, or DELETE operations.

This information is valid in the cluster system.

The following is an example of retrieving the CLUSTER_GROUP_NAME pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_GROUP_NAME FROM T1;
C1 T1.CLUSTER_GROUP_NAME
-- ---------------------
A  G1                   
B  G2                   
C  G3                   

3 rows selected.

CLUSTER_MEMBER_NAME Pseudo Column

The CLUSTER_MEMBER_NAME pseudo column returns the member name of the server where the record is stored.

The CLUSTER_MEMBER_NAME pseudo column can be used in SELECT operations, but can not be used in INSERT, UPDATE, or DELETE operations.

This information is valid in the cluster system.

The following is an example of retrieving the CLUSTER_MEMBER_NAME pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_MEMBER_NAME FROM T1;
C1 T1.CLUSTER_MEMBER_NAME
-- ----------------------
A  G1N1                  
B  G2N1                  
C  G3N1                  

3 rows selected.

CLUSTER_SHARD_ID Pseudo Column

The CLUSTER_SHARD_ID pseudo column returns the shard identifier where the record is stored.
The CLUSTER_SHARD_ID pseudo column is allowed for SELECT statements only, it can not be used in INSERT, UPDATE, or DELETE statements.

This information is valid in the cluster system.

The following is an example of how to retrieve the CLUSTER_SHARD_ID pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_SHARD_ID FROM T1;

C1 CLUSTER_SHARD_ID
-- ----------------
A                14
B                17
C                 4

3 rows selected.

Compatibility

The SQL standard compatibility for the pseudo column is as follows.
SQL standard compatibility for pseudo column

Feature ID

Description

Availability

T176

Sequence generator support

O

T177

Sequence generator support: simple restart option

O

Operators

An operator is represented by one or more specific symbols or keywords in the syntax, and it performs an operation on one or more arguments.
The types of operators are as follows.
• Arithmetic operator
• Concatenation operator
• Set operator

Arithmetic Operator

Syntax

<arithmetic operator> ::=
        <value term>
      | <expression> + <value term>
      | <expression> - <value term>

<value term> ::=
        <value factor>
      | <value term> * <value factor>
      | <value term> / <value factor>

<value factor> ::=
        <expression>
      | + <expression>
      | - <expression>

Description

An arithmetic operator performs arithmetic operations on numeric types, date/time types, or interval types.

The precedence of arithmetic operators is as follows.

  1. + (POSITIVE), - (NEGATIVE)

  2. * (MULTIPLICATION), / (DIVISION)

  3. + (ADDITION), - (SUBTRACTION)

Concatenation Operator

Syntax

<concatenation operator> ::=
        <expression> || <expression>

Description

The concatenation operator returns a string that connects the values of the CHARACTER STRING or BINARY STRING types.
For more information, refer to || (CONCATENATE), CONCATENATE.

Set Operator

Syntax

<set operator> ::=
        <set operator term>
      | <subquery> UNION [ ALL | DISTINCT ] <set operator term>
      | <subquery> EXCEPT [ ALL | DISTINCT ] <set operator term>
      | <subquery> MINUS [ ALL | DISTINCT ] <set operator term>

<set operator term> ::=
        <subquery>
      | <subquery> INTERSECT [ ALL | DISTINCT ] <set operator term>

Description

A set operator performs a set operation on the results of a subquery.

INTERSECT ALL/DISTINCT has higher precedence than other set operators.

Set operators

Operator

Description

UNION ALL

It is the union that does not exclude duplicate rows from the subquery result.

UNION DISTINCT

It is the union that excludes duplicate rows from the subquery result.

EXCEPT ALL

It is the difference set that does not exclude duplicate rows from the subquery result.

EXCEPT DISTINCT

It is the difference set that excludes duplicate rows from the subquery result.

MINUS ALL

It is the same as EXCEPT ALL.

MINUS DISTINCT

It is the same as EXCEPT DISTINCT.

INTERSECT ALL

It is the intersection that does not exclude duplicate rows from the subquery result.

INTERSECT DISTINCT

It is the intersection that excludes duplicate rows from the subquery result.

Compatibility

The SQL standard compatibility for operators is as follows.

SQL standard compatibility for operators

Feature ID

Description

Availability

E011-04

Arithmetic operators

O

E021-07

Character concatenation

O

E071-01

UNION DISTINCT table operator

O

E071-02

UNION ALL table operator

O

E071-03

EXCEPT DISTINCT table operator

O

E071-05

Columns combined via table operators need not have exactly the same data type

O

E071-06

Table operators in subqueries

O

F041-08

All comparison operators are supported (rather than just =)

O

F303

INTERSECT DISTINCT table operator

O

F305

INTERSECT ALL table operator

O

F304

EXCEPT ALL table operator

O

F846

Octet support in regular expression operators

X

J571

NEW operator

X

Functions

Although operators and functions are similar in functionality, a function specifies its arguments by using parentheses after its name. A function can accept zero or more arguments.
The function has two types, as follows.
.• Single row function
• Aggregate function

Single Row Function

A single-row function returns one result row for each row in the table or view.

The single-row functions are as follows.

Numeric Functions

A numeric value is input into a numeric function, which returns a numeric result.

For more information about numeric function types, refer to the following.

Character String Functions Returning Character Values

A character string value is input into character string functions returning character values, and the function returns a result of character string type.

For more information about character string functions returning character values, refer to the following.

Character String Functions Returning Number Values

A character string value is input into character string functions returning number values, and the function returns a result of number type.

For more information about character string functions returning number values types, refer to the following.

Datetime Functions

The value of the date/time/timestamp/interval type is input into a datetime function, and the function returns a result of the date/time/timestamp/interval type.

For more information about datetime function types, refer to the following.

General Comparison Functions

A general comparison function returns either the minimum or maximum value from a set of values.

For more information about general comparison function types, refer to the following.

Conversion Functions

A conversion function sets a value to a specific data type.

For more information about the types of conversion functions, refer to the following.

Conditional Functions

A conditional function returns a specific value based on a condition.

For more information about the types of conditional functions, refer to the following.

NULL-related Functions

A NULL-related function returns a specific value based on whether the input value is NULL.

For more information about the types of NULL-related functions, refer to the following.

ROWID-related Functions

A ROWID-related function is used to retrieve information about the ROWID.

For more information about the types of ROWID-related functions, refer to the following.

Encryption Functions

The encryption function encrypts, decrypts, or hashes the given plain text using a specific algorithm and then returns the result.

The types of encryption functions are as follows.

System Information Functions

The system information function is used to obtain information about sessions and the system.

For more information about the types of system information functions, refer to the following.

Statistics Information function

A statistics information function is used to retrieve information about an object.

The types of statistics information functions are as follows.

Aggregate Function

An aggregate function generates a single result row for multiple rows.

For more information about aggregate function types, refer to the following.

Window Function

It returns the result of the function for the defined range of records.

The defined range of records is called a window, and the execution range is defined using OVER <window name or specification>.

For more information about the window, refer to the window clause.

Each record within the group contains the result of executing the window function over the defined window (range). Therefore, unlike an aggregate function, the window function returns multiple records for each group.

The window function is available in the select list and the order by clause.

The window functions are listed below.

Compatibility

The SQL standard compatibility for functions is as follows.

SQL standard compatibility for functions

Feature ID

Description

Availability

B033

Untyped SQL-invoked function arguments

X

E021-04

CHARACTER_LENGTH function

O

E021-05

OCTET_LENGTH function

O

E021-06

SUBSTRING function

O

E021-08

UPPER and LOWER functions

O

E021-09

TRIM function

O

E021-11

POSITION function

O

E091-01

AVG

O

E091-02

COUNT

O

E091-03

MAX

O

E091-04

MIN

O

E091-05

SUM

O

E091-06

ALL quantifier

O

E091-07

DISTINCT quantifier

O

F131-03

Set functions supported in queries with grouped views

O

F201

CAST function

O

F441

Extended set function support

O

F442

Mixed column references in set functions

X

F801

Full set function

X

F842

OCCURRENCES_REGEX function

X

F843

POSITION_REGEX function

X

S071

SQL paths in function and type name resolution

X

S201-02

Array as result type of functions

X

S211

User-defined cast functions

X

S241

Transform functions

X

T041-03

POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types

X

T312

OVERLAY function

O

T321-01

User-defined functions with no overloading

O

T326

Table functions

X

T341

Overloading of SQL-invoked functions and SQL-invoked procedures

X

T433

Multiargument GROUPING function

X

T441

ABS and MOD functions

O

T571

Array-returning external SQL-invoked functions

X

T572

Multiset-returning external SQL-invoked functions

X

T581

Regular expression substring function

X

T614

NTILE function

O

T615

LEAD and LAG functions

O

T616

Null treatment option for LEAD and LAG functions

O

T617

FIRST_VALUE and LAST_VALUE functions

O

T618

NTH_VALUE function

O

T619

Nested window functions

X

T621

Enhanced numeric functions

O

Conditions

Condition

A condition is an expression that is evaluated as TRUE, FALSE, or UNKNOWN.
A condition can be used in the following SQL statements.

• WHERE clauses in DELETE, UPDATE statements
• WHERE and HAVING clauses in SELECT statement
• Where the BOOLEAN TYPE can be used
The condition types are as follows.

• Comparison condition
• Logical condition
• Null condition
• Compound condition
• Pattern-matching condition
• Between condition
• In condition
• Exists condition
• Distinct condition
Condition precedence

Precedence

Condition type

1

Operators in condition clauses

2

=, !=, <, >, <=, >=

3

IS [NOT] NULL,

[NOT] BETWEEN,

[NOT] IN,

LIKE, EXISTS,

IS [NOT] DISTINCT FROM

4

NOT

5

AND

6

OR

Comparison Conditions

It compares both conditional expressions and returns a boolean value of TRUE, FALSE, or UNKNOWN.
Comparison conditions

Condition

Description

=

It checks if both conditions are equal.

!=, <>

It checks if both conditions are not equal.

>

It compares which one of the two conditions is greater.

<

It compares which one of the two conditions is smaller.

>=

It compares which one of the two conditions is greater or equal.

<=

It compares which one of the two conditions is smaller or equal.

ANY, SOME

If there is a condition whose left expr satisfies at least one of the right expr_list (or subquery results), then it returns TRUE.

If there is no right subquery result, then it returns FALSE.

ALL

If there is a condition whose left expr satisfies all the right expr_list (or subquery results), then it returns TRUE.

If there is no right subquery result, then it returns TRUE.

For more information, refer to Type Comparison.

< Simple Comparison Conditions >

Syntax

<simple_comparison_condition> ::=
        <expr>          <comparison_operator> <expr>
      | <expr>          <comparison_operator> ( <subquery> )
      | ( <subquery> )  <comparison_operator> <expr>
      | ( <subquery> )  <comparison_operator> ( <subquery> ) 
      | ( <expr_list> ) <comparison_operator> ( <expr_list> )
      | ( <expr_list> ) <comparison_operator> ( <subquery> )
      | ( <subquery> )  <comparison_operator> ( <expr_list> )
      | ( <subquery> )  <comparison_operator> ( <subquery> )

<comparison_operator> ::=
        <  =   >
      | <  !=  >
      | <  <   >
      | <  >   >
      | <  <=  >
      | <  >=  >

<expr_list> ::= 
        <expr>
      | <expr>, ... , <expr>
      | ( <expr> )
      | ( <expr> , ... , <expr> )
For more information, refer to Scalar Subquery Expression.

Description

If the expr list or subquery appears on both sides of the comparison_operator, the number of expr or subquery targets to be compared must be same.
If there is a subquery, the number of result records must be one.

Example

Example of simple comparison conditions

Conditional expression

Result

'abc' = 'abc'

TRUE

'abc' != 'abc'

FALSE

'abc' < 'abc'

FALSE

'abc' <= 'abc'

TRUE

'abc' > 'abc'

FALSE

'abc' >= 'abc'

TRUE

( 1, 2, 3 ) = ( 1, 2, 3 )

TRUE

( 1, 2, 3 ) = ( 1, 2, 4 )

FALSE

( 1, 2, 3 ) != ( 4, 5, 6 )

TRUE

( 1, 2, 3 ) != ( 1, 2, 3 )

FALSE

( 1, 2, 3 ) < ( 1, 2, 4 )

TRUE

( 1, 2, 3 ) < ( 1, 2, 3 )

FALSE

( 1, 2, 3 ) <= ( 1, 2, 4 )

TRUE

( 1, 2, 3 ) <= ( 1, 2, 2 )

FALSE

( 1, 2, 3 ) > ( 1, 2, 2 )

TRUE

( 1, 2, 3 ) > ( 1, 2, 4 )

FALSE

( 1, 2, 3 ) >= ( 1, 2, 2 )

TRUE

( 1, 2, 3 ) >= ( 1, 2, 4 )

FALSE

<Group Comparison Conditions>

Syntax

<group_comparison_condition> ::=
   <expr>          <comparison_operator> <quantifier> ( <expr_list> )
 | <expr>          <comparison_operator> <quantifier> ( <subquery> )
 | ( <expr_list> ) <comparison_operator> <quantifier> ( <expr_list_list> )
 | ( <expr_list> ) <comparison_operator> <quantifier> ( <subquery> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <expr_list> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <expr_list_list> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <subquery> )

<comparison_operator> ::=
        <  =   >
      | <  !=  >
      | <  <   >
      | <  >   >
      | <  <=  >
      | <  >=  >

<quantifier> ::=
        ALL
      | ANY
      | SOME

<expr_list> ::= 
        <expr>
      | <expr>, ... , <expr>
      | ( <expr> )
      | ( <expr> , ... , <expr> )

<expr_list_list> ::=
        <expr_list>
      | <expr_list>, ... , <expr_list>
For more information, refer to Scalar Subquery Expression.

Description

If the expr list or subquery appears on both sides of the comparison_operator, the number of expr or subquery targets to be compared must be the same.
If a subquery appears on the left side of the comparison_operator, the number of result records must be one.
If a subquery appears on the right side of the comparison_operator, the number of result records can be multiple.

Example

Example of group comparison conditions

Conditional expression

Result

1 =any ( 1, 2, 3, 4, 5 )

TRUE

1 =any ( 1, 2, null, 4, 5 )

TRUE

1 =any ( 2, null, 4, 5 )

NULL

1 =any ( 100, 2, 3, 4, 5 )

FALSE

1 =all ( 1, +1, 1E+0 )

TRUE

1 =all ( 1, +1, 1E+0, null )

NULL

1 =all ( 1, 2, 3, 4, 5 )

FALSE

( 1, 2 ) =any ( ( 0, 1 ), ( 1, 2 ), ( 3, 4 ) )

TRUE

( 1, 2 ) =any ( ( 0, 1 ), ( 1, 2 ), ( null, null ) )

TRUE

( 1, 2 ) =any ( ( 0, 1 ), ( 2, 3 ), ( 3, 4 ) )

FALSE

( 1, 2 ) =all ( ( 1, 2 ), ( +1, +2 ), ( 1E+0, 2E+0 ) )

TRUE

( 1, 2 ) =all ( ( 1, 2 ), ( +1, +2 ), ( null, null ) )

NULL

( 1, 2 ) =all ( ( 0, 1 ), ( 2, 3 ), ( 3, 4 ) )

FALSE

When the result record of comparison_operator's right subquery is 0

( 'X' ) =any ( select dummy from dual where dummy = 'Y' )

FALSE

( 'X' ) =all ( select dummy from dual where dummy = 'Y' )

TRUE

Logical Conditions

Logical conditions include AND, OR, and NOT.

AND

Syntax

<boolean value expression> AND <boolean value expression>

Description

Truth table of AND boolean operator

AND

True

False

Unknown

True

True

False

Unknown

False

False

False

False

Unknown

Unknown

False

Unknown

OR

Syntax

<boolean value expression> OR <boolean value expression>

Description

Truth table of OR boolean operator

OR

True

False

Unknown

True

True

True

True

False

True

False

Unknown

Unknown

True

Unknown

Unknown

NOT

Syntax

NOT <boolean value expression>

Description

Truth table of NOT boolean operator

expr

NOT

True

False

False

True

Unknown

Unknown

Null Condition

Syntax

<expr> IS [NOT] NULL

Description

It checks whether the result value of expr is NULL.
Result table of IS NULL condition

expr

IS NULL

IS NOT NULL

NULL

True

False

NOT NULL

False

True

Compound Conditions

It is a conditional expression that combines multiple conditions.

compound_condition ::=
        ( condition )
      | NOT condition
      | condition < AND | OR > condition

Pattern-matching Conditions

Like Condition

Syntax

like_condition ::=
        string [NOT] LIKE pattern [ ESCAPE escape_character ]

Description

It checks whether a string matches the specified pattern.
Arguments such as string, pattern, and escape_character can be of a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or any type that can be converted to a character type.
If string, pattern, or escape_character is NULL, it returns NULL.
If the escape_character is omitted, there is no default value.
If the escape_character is specified, it should be a single character.
If pattern does not include '_' or '%', it is processed the same way as an equal operation (string = pattern).
If the pattern includes '_'  or '%', the string is checked for a match as follows.
• '_': It corresponds to any single character.
• '%': It corresponds to any string of zero or more characters.
To compare the '_' or '%' included in the pattern as literal characters, use the ESCAPE clause.
Specify the escape_character and place it before the '_' or '%' in the pattern.

Example

gSQL> SELECT 'hello%' LIKE 'h%o!%' ESCAPE '!' AS RESULT FROM DUAL;
RESULT
------
TRUE

• 'represent' LIKE 'represent'   => TRUE
• 'represent' LIKE ' represent ' => FALSE
• 'represent' LIKE 'REPRESENT'   => FALSE
• 'represent' LIKE 'r_pr_s_nt'   => TRUE
• 'represent' LIKE 're%t'        => TRUE
• 'represent' LIKE 'rep'         => FALSE

• 'summer_vacation' LIKE 'summer\_vacation' ESCAPE '\'  => TRUE
• NULL LIKE 'summer\_vacation' ESCAPE '\'               => NULL
• 'summer_vacation' LIKE NULL ESCAPE '\'                => NULL
• 'summer_vacation' LIKE 'summer\_vacation' ESCAPE NULL => NULL

REGEXP_LIKE Condition

Syntax

regexp_like_condition ::=
        REGEXP_LIKE ( source_string, pattern[, match_param ] )

Description

It checks whether the source_string matches the specified pattern.
source_string
It is the character expression of the search target, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
pattern
It is the regular expression, and it can be of a character type or a type that can be converted to a character such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
It can be described up to 512 bytes.
For more information about the operators that can be specified in the pattern, refer to Regular Expression.
match_param
It is the character expression that can alter the default matching operation of a function, and it can be of a character type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
'i', 'c', 'n', 'm', 'x' can be specified in match_param, and one or more of them can be described.
'i'

It is case-insensitive.

'c'

It is case-sensitive.

'n'

The dot operator ( . ) allows matching with the newline character.

'm'

It processes the source_string as multiple lines. It interprets ^ ( Beginning-of-Line Anchor ) and $ ( End-of-Line Anchor ) for each line.

'x'

It ignores whitespace in the pattern.

If a character other than 'i', 'c', 'n', 'm', or 'x' appears in match_param, an error is returned. 
If contradictory case matching, such as 'ic', is listed in match_param, an error is returned.
If match_param is omitted,
 • It is case-sensitive.
 • The dot operator ( . ) does not allow matching with the newline character.
 • It processes the source_string as a single line.

Example

gSQL> 
SELECT web_street_name FROM web_site;
WEB_STREET_NAME
---------------
Dogwood Sunset 
7th            
2nd 3rd        
Hill 5th       
Cedar North    
5 rows selected.

gSQL> 
SELECT *
  FROM web_site
 WHERE REGEXP_LIKE( web_street_name, '[[:digit:]]th$' );
WEB_STREET_NAME
---------------
7th            
Hill 5th       
2 rows selected.

BETWEEN Condition

Syntax

<between condition> ::=
   <expr1> [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <expr2> AND <expr3>

Description

It checks whether expr1 is within the range between expr2 and expr3.
If ASYMMETRIC or SYMMETRIC is omitted, the default is ASYMMETRIC.
If the data types of expr1, expr2, and expr3 are different, they will be converted. 
For more information, refer to Type Comparison, Type Conversion.
Equivalence of the BETWEEN clause

A

B

X BETWEEN ASYMMETRIC Y AND Z

X BETWEEN Y AND Z

X BETWEEN Y AND Z

X >= Y AND X <= Z

X NOT BETWEEN Y AND Z

NOT( X BETWEEN Y AND Z )

X BETWEEN SYMMETRIC Y AND Z

((X BETWEEN Y AND Z) OR (X BETWEEN Z AND Y)

X NOT BETWEEN SYMMETRIC Y AND Z

NOT( X BETWEEN SYMMETRIC Y AND Z )

Example

Example of BETWEEN clause

Conditional expression

Result

BETWEEN [ ASYMMETRIC ]

3 BETWEEN 1 AND 5

TRUE

NULL BETWEEN 1 AND 5

3 BETWEEN NULL AND 5

3 BETWEEN 1 AND NULL

NULL

3 BETWEEN 5 AND 1

FALSE

BETWEEN SYMMETRIC

3 BETWEEN SYMMETRIC 1 AND 5

TRUE

NULL BETWEEN SYMMETRIC 1 AND 5

3 BETWEEN SYMMETRIC NULL AND 5

3 BETWEEN SYMMETRIC 1 AND NULL

NULL

3 BETWEEN SYMMETRIC 5 AND 1

TRUE

IN Condition

Syntax

<in_condition> ::=
        <expr>          [NOT] IN ( <expr_list> )  
      | <expr>          [NOT] IN ( <subquery> )  
      | ( <expr_list> ) [NOT] IN ( <expr_list_list> )  
      | ( <expr_list> ) [NOT] IN ( <subquery> )
      | ( <subquery> )  [NOT] IN ( <expr_list> )  
      | ( <subquery> )  [NOT] IN ( <expr_list_list> )  
      | ( <subquery> )  [NOT] IN ( <subquery> )  

<expr_list> ::=          
        <expr>       
      | <expr>, ... , <expr>       
      | ( <expr> )       
      | ( <expr> , ... , <expr> )  

<expr_list_list> ::=         
        <expr_list>       
      | <expr_list>, ... , <expr_list>

Description

The IN condition returns the same result as = ANY.
The NOT IN condition returns the same result as !=ALL.
For more information, refer to Comparison Conditions.

Example

Example of IN condition

Conditional expression

Result

1 IN ( 1, 2, 3, 4, 5 )

TRUE

1 IN ( 1, 2, null, 4, 5 )

TRUE

1 IN ( 2, null, 4, 5 )

NULL

1 IN ( 100, 2, 3, 4, 5 )

FALSE

NULL IN ( 1, 2, 3 )

NULL

1 NOT IN ( 2, 3, 4, 5 )

TRUE

1 NOT IN ( 2, null, 4, 5 )

NULL

1 NOT IN ( 1, 2, null, 4, 5 )

FALSE

1 NOT IN ( 100, 2, 3, 4, 5 )

TRUE

NULL NOT IN ( 1, 2, 3 )

NULL

EXISTS Condition

Syntax

exists_conditions ::= 
        EXISTS ( subquery )

Description

It checks whether the result record of the subquery exists. 
If the subquery returns a result record, it returns TRUE. Otherwise, it returns FALSE.

Example

gSQL> SELECT * FROM DUAL WHERE EXISTS ( SELECT * FROM DUAL );
      DUMMY
      -----
      X    
      1 row selected.

gSQL> SELECT * FROM DUAL 
       WHERE EXISTS ( SELECT * FROM DUAL WHERE DUMMY = 'Y' );
      no rows selected.

DISTINCT Condition

Syntax

distinct_conditions ::= 
        <expr> IS [NOT] DISTINCT FROM <expr>
      | ( <expr_list> ) IS [NOT] DISTINCT FROM ( <expr_list> )

Description

The operand types in a distinct condition must be comparable to each other.
If the operand is <expr_list>, the data at the same position becomes the comparison target.
When all operands of a DISTINCT condition are not null value,
is distinct from returns the same result as not equal (!=)
and is not distinct from returns the same result as equal (=).
The DISTINCT condition treats NULL values as regular data, rather than as unknown, which distinguishes it from other comparison operators.

Example

* IS DISTINCT FROM

gSQL>
SELECT i1,
       i2,
       i1 IS DISTINCT FROM i2 AS IsDistinct
  FROM t1; 

  I1   I2 ISDISTINCT
---- ---- ----------
   1 null TRUE      
   1    1 FALSE     
   1    2 TRUE      
null null FALSE     
null    1 TRUE      
null    2 TRUE      

6 rows selected.

gSQL> 
SELECT i1,
       i2,
       i3,
       ( I1, I2, I3 ) IS DISTINCT FROM ( 1, 1, 1 ) AS RESULT 
  FROM t1;

  I1   I2   I3 RESULT
---- ---- ---- ------
   1    1    1 FALSE 
   2 null    3 TRUE  
null null null TRUE  

3 rows selected.


* IS NOT DISTINCT FROM

gSQL>
SELECT i1,
       i2,
       i1 IS NOT DISTINCT FROM i2 AS IsNotDistinct 
  FROM t1; 

  I1   I2 ISNOTDISTINCT
---- ---- -------------
   1 null FALSE        
   1    1 TRUE         
   1    2 FALSE        
null null TRUE         
null    1 FALSE        
null    2 FALSE        

6 rows selected.

gSQL> 
SELECT i1,
       i2,
       i3,
       ( I1, I2, I3 ) IS NOT DISTINCT FROM ( 1, 1, 1 ) AS RESULT
 FROM t1;

  I1   I2   I3 RESULT
---- ---- ---- ------
   1    1    1 TRUE  
   2 null    3 FALSE 
null null null FALSE 

3 rows selected.

Compatibility

The SQL standard compatibility for conditions is as follows.

SQL standard compatibility for conditions

Feature ID

Description

Availability

E061-01

Comparison predicate

O

E061-02

BETWEEN predicate

O

E061-03

IN predicate with list of values

O

E061-04

LIKE predicate

O

E061-05

LIKE predicate: ESCAPE clause

O

E061-06

NULL predicate

O

E061-07

Quantified comparison predicate

O

E061-08

EXISTS predicate

O

E061-09

Subqueries in comparison predicate

O

E061-11

Subqueries in IN predicate

O

E061-12

Subqueries in quantified comparison predicate

O

E061-13

Correlated subqueries

O

E061-14

Search condition

O

F051-04

Comparison predicate on DATE, TIME, and TIMESTAMP data types

X

F053

OVERLAPS predicate

X

F263

Comma-separated predicates in simple CASE expression

X

F291

UNIQUE predicate

X

F481

Expanded NULL predicate

O

F841

LIKE_REGEX predicate

X

P008

Comma-separated predicates in a CASE statement Extended CASE

X

S151

Type predicate

X

T141

SIMILAR predicate

X

T151

DISTINCT predicate

X

T152

DISTINCT predicate with negation

X

T461

Symmetric BETWEEN predicate

O

T501

Enhanced EXISTS predicate

O

T631

IN predicate with one list element

X

X090

XML document predicate

X

X091

XML content predicate

X

X141

IS VALID predicate: data-driven case

X

X142

IS VALID predicate: ACCORDING TO clause

X

X143

IS VALID predicate: ELEMENT clause

X

X144

IS VALID predicate: schema location

X

X145

IS VALID predicate outside check constraints

X

X151

IS VALID predicate with DOCUMENT option

X

X152

IS VALID predicate with CONTENT option

X

X153

IS VALID predicate with SEQUENCE option

X

X155

IS VALID predicate: NAMESPACE without ELEMENT clause

X

X157

IS VALID predicate: NO NAMESPACE with ELEMENT clause

X

Regular Expression

A regular expression defines a search pattern using meta-characters (operators) and character literals.

July (fourth|4(th)?)

Regular Expressions

Regular Expression Matching Options

The options specifying the matching operation of the regular expression are as follows.

* i : case-insensitive matching

gSQL>
SELECT REGEXP_COUNT( 'Superscript digits', 's', 1, 'i' ) AS RESULT
  FROM dual;
RESULT
------
     3
1 row selected.
* c : case-sensitive matching

gSQL> 
SELECT REGEXP_COUNT( 'Superscript digits', 's', 1, 'c' ) AS RESULT
  FROM dual;
RESULT
------
     2
1 row selected.
* n : Dot operator(.) allows the match with the newline character.

gSQL> 
SELECT * FROM t1;
C1               
-----------------
matching options:
i, c, n, m, x    
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, ':.+', 1, 1, 'n' ) AS RESULT
  FROM t1;
RESULT       
-------------
:            
i, c, n, m, x
1 row selected.
* m : It specifies the newline character within the string as the multiline mode terminating the row.

gSQL> 
SELECT * FROM t1;
C1               
-----------------
matching options:
i, c, n, m, x    
1 row selected.

gSQL> 
SELECT REGEXP_COUNT( c1, '^.+', 1, 'm' ) AS RESULT
  FROM t1;
RESULT
------
     2
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, '^.+', 1, 1, 'm' ) AS RESULT1,
       REGEXP_SUBSTR( c1, '^.+', 1, 2, 'm' ) AS RESULT2
  FROM t1;
RESULT1           RESULT2      
----------------- -------------
matching options: i, c, n, m, x
1 row selected.
* x : It ignores the whitespace within the regular expression.

gSQL> 
SELECT REGEXP_SUBSTR('MATCHING', 'M A T C H I N G', 1, 1, 'x') AS RESULT
  FROM dual;
RESULT  
--------
MATCHING
1 row selected.

Regular Expression Operators

The operator processes data from the database character set and includes multibyte characters in the match.
Regular Expression Operators

Operator

Description

\

escape character

The backslash (\) treats the character following it as a literal.

  • It can search for an operator as a character.

    • e.g. \+ (It searches for + as a character.)

    • e.g. \\ (It searches for \ as a character.)

.

It matches one character.

*

It matches zero or more. (greedy)

+

It matches one or more. (greedy)

?

It matches 0 or 1 ( zero or one ). (greedy)

|

Alternation: It matches one of several expressions.

^

  • Default mode: It matches the beginning of the string.

    • In the string abc\ndef, the regular expression ^. matches a.

  • Multiline mode (matching option 'm'): It matches the beginning of each row in the string.

    • In the string abc\ndef, the regular expression ^. matches a and d.

$

  • Default mode: It matches the character at the end of the string, or the character immediately before \n at the end of the string.

    • e.g. In abc\ndef, abc\ndef\n strings, the regular expression .$ matches all occurrences of f.

  • Multiline mode (matching option 'm'): It matches the end of each row in the string.

    • e.g. In abc\ndef string, the regular expression .$ matches c and f.

[ ]

It matches one of the characters from the list within [ ].

  • single character, - (range), [: :] (character class) can be included in the character list within [ ].

    • e.g. REGEXP_SUBSTR( 'aB9', '[aA-Z[:digit:]]*' ) ==> aB9

  • The characters specified by - (range) are compared based on their Unicode binary codes.

  • All characters are interpreted as literals, except for - (range), [: :] (character class).

  • To include the ] (right bracket) character in the character list, it should be placed first in the list.

    • e.g. REGEXP_SUBSTR( ']', '[]Bracket]' ) ==> ]

  • To include the - (hyphen) character in the character list, it should be placed first or last in the list.t.

    • e.g. REGEXP_SUBSTR( '-', '[-Bracket]' ) ==> -

    • e.g. REGEXP_SUBSTR( '-', '[Bracket-]' ) ==> -

[^ ]

It matches characters that are not present in the character list inside [ ].

  • The character list inside [ ] can include single characters, - (range), and [: :] (character class).

    • e.g. REGEXP_SUBSTR( 'aB9', '[^aA-Z[:digit:]]*' ) ==> NULL

  • The characters specified by - (range) are compared based on their Unicode binary codes.

  • All characters are interpreted as literals, except for - (range) and [: :] (character class).

*To include the ] (right bracket) character in the character list, it should be placed after the ^.

  • N/A

    • e.g. REGEXP_SUBSTR( ']', '[^]Bracket]' ) ==> NULL

  • To include the - (hyphen) character in the character list, it should be placed after the ^ or at the end of the list.

    • e.g. REGEXP_SUBSTR( '-', '[^-Bracket]' ) ==> NULL

    • e.g. REGEXP_SUBSTR( '-', '[^Bracket-]' ) ==> NULL

( )

It treats the expression within ( ) as a subexpression group.

A subexpression can be included within another subexpression.

Subexpressions are numbered from left to right, starting with the opening parenthesis ( of the subexpression.

  • (abc)((12(345))67(89))

    • 1 : abc

    • 2 : 123456789

    • 3 : 12345

    • 4 : 345

    • 5 : 89

{m}

It matches m times.

{m,}

It matches at least m times. (greedy)

{m,n}

It matches at least m times and at most n times. (greedy)

\n

Back Reference

It matches the n-th subexpression defined before the back reference.

n is an integer between 1 and 9.

The back reference counts subexpressions from left to right, starting with the opening parenthesis ( of each subexpression.

b[: :]

It matches characters that belong to a specified character class.

  • [:alnum:] : Alphabetic characters and digits

  • [:alpha:] : Alphabetic characters

  • [:blank:] : Whitespace and tab

  • [:cntrl:] : Control characters

  • [:digit:] : Digits

  • [:graph:] : Printable characters, excluding space

  • [:lower:] : Lowercase letters

  • [:print:] : Printable characters, including space

  • [:punct:] : Special characters

  • [:space:] : Whitespace characters

  • [:upper:] : Uppercase letters

  • [:xdigit:] : Hexadecimal digits

\d

It matches a digit character.

It is the same as [[:digit:]].

\D

It matches a character that is not a digit.

It is the same as [^[:digit:]].

\w

It matches an alphanumeric character or an underscore.

It is the same as [[:alnum:]_].

\W

It matches a character that is not a word character.

It is the same as [^[:alnum:]_].

\s

It matches a whitespace character.

It is the same as [[:space:]].

\S

It matches a character that is not a whitespace character.

It is the same as [^[:space:]].

\A

It matches the start of the string.

\Z

It matches the character at the end of the string or the character immediately before \n at the end of the string.

e.g. The regular expression .\Z matches the character f in both abc\ndef and abc\ndef\n.

\z

It matches the end of the string.

*?

It matches 0 or more times. (nongreedy)

+?

It matches one or more times. (nongreedy)

??

It matches 0 or 1 time. (nongreedy)

{m}?

It matches m times. (nongreedy)

{m,}?

It matches at least m times. (nongreedy)

{m,n}?

It matches at least m times and at most n times. (nongreedy)

### e.g.

gSQL> 
SELECT REGEXP_SUBSTR( 'axxxbxbxb', 'a\w+b' ) AS RES_GREEDY,
       REGEXP_SUBSTR( 'axxxbxbxb', 'a\w+?b' ) AS RES_NON_GREEDY
  FROM dual;
RES_GREEDY RES_NON_GREEDY
---------- --------------
axxxbxbxb  axxxb         
1 row selected.

The following are examples of using each regular expression operator.

• \

gSQL> 
SELECT REGEXP_SUBSTR( '(a)\1', '\(a\)\\1' ) AS RESULT FROM dual;
RESULT
------
(a)\1 
1 row selected.
•.

gSQL> SELECT REGEXP_SUBSTR( 'abcde', 'a...e' ) AS RESULT FROM dual;
RESULT
------
abcde 
1 row selected.
• *, +, ?

gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'ax*b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axb', 'ax*b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxb', 'ax*b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
ab      axb     axxxb  
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'ax+b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axb', 'ax+b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxb', 'ax+b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
null    axb     axxxb  
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'ax?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axb', 'ax?b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxb', 'ax?b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
ab      axb     null   
1 row selected.
• |

gSQL> 
SELECT REGEXP_SUBSTR( 'Regexp', '(R|r)egexp' ) AS RESULT1,
       REGEXP_SUBSTR( 'regexp', '(R|r)egexp' ) AS RESULT2
 FROM dual;
RESULT1 RESULT2
------- -------
Regexp  regexp 
1 row selected.
• ^, $

gSQL> 
SELECT * FROM t1;
C1             
---------------
Line1 : aaa xy1
Line2 : bbb xy2
Line3 : ccc xy3
1 row selected.

gSQL> 
SELECT REGEXP_COUNT( c1, '^Line[[:digit:]]' ) AS RESULT_DEFAULT_MODE,
       REGEXP_COUNT( c1, '^Line[[:digit:]]', 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
                  1                     3
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, '^Line[[:digit:]]', 1 ) AS RES_DEFAULT,
       REGEXP_SUBSTR( c1, '^Line[[:digit:]]', 1, 1, 'm' ) AS RES_MULTILINE_1,
       REGEXP_SUBSTR( c1, '^Line[[:digit:]]', 1, 2, 'm' ) AS RES_MULTILINE_2,
       REGEXP_SUBSTR( c1, '^Line[[:digit:]]', 1, 3, 'm' ) AS RES_MULTILINE_3
  FROM t1;
RES_DEFAULT RES_MULTILINE_1 RES_MULTILINE_2 RES_MULTILINE_3
----------- --------------- --------------- ---------------
Line1       Line1           Line2           Line3          
1 row selected.


gSQL> 
SELECT REGEXP_COUNT( c1, 'xy[[:digit:]]$' ) AS RESULT_DEFAULT_MODE,
       REGEXP_COUNT( c1, 'xy[[:digit:]]$', 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
                  1                     3
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, 'xy[[:digit:]]$', 1 ) AS RES_DEFAULT,
       REGEXP_SUBSTR( c1, 'xy[[:digit:]]$', 1, 1, 'm' ) AS RES_MULTILINE_1,
       REGEXP_SUBSTR( c1, 'xy[[:digit:]]$', 1, 2, 'm' ) AS RES_MULTILINE_2,
       REGEXP_SUBSTR( c1, 'xy[[:digit:]]$', 1, 3, 'm' ) AS RES_MULTILINE_3
  FROM t1;
RES_DEFAULT RES_MULTILINE_1 RES_MULTILINE_2 RES_MULTILINE_3
----------- --------------- --------------- ---------------
xy3         xy1             xy2             xy3            
1 row selected.
• [ ], [^ ]

gSQL> 
SELECT REGEXP_SUBSTR( 'a', '[abc]' ) AS RESULT1,
       REGEXP_SUBSTR( 'A', '[A-Z]' ) AS RESULT2,
       REGEXP_SUBSTR( '1', '[[:digit:]]' ) AS RESULT3,
       REGEXP_SUBSTR( 'abAB12', '[abcA-Z[:digit:]]+' ) AS RESULT
  FROM dual;
RESULT1 RESULT2 RESULT3 RESULT
------- ------- ------- ------
a       A       1       abAB12
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'x', '[^abc]' ) AS RESULT1,
       REGEXP_SUBSTR( 'y', '[^A-Z]' ) AS RESULT2,
       REGEXP_SUBSTR( 'z', '[^[:digit:]]' ) AS RESULT3,
       REGEXP_SUBSTR( 'xyz', '[^abcA-Z[:digit:]]+' ) AS RESULT
  FROM dual;
RESULT1 RESULT2 RESULT3 RESULT
------- ------- ------- ------
x       y       z       xyz   
1 row selected.
• ( ), \n

gSQL> 
SELECT REGEXP_SUBSTR( 'abcbcabc', 'a(bc)\1a\1' ) AS RESULT1,
       REGEXP_SUBSTR( 'abcbcabc', '(a)(b)(c)\2\3\1\2\3' ) AS RESULT2,
       REGEXP_SUBSTR( 'abcbcabc', '(a(bc))\2\1' ) AS RESULT3
 FROM dual;
RESULT1  RESULT2  RESULT3 
-------- -------- --------
abcbcabc abcbcabc abcbcabc
1 row selected.
• {m}, {m,}, {m,n}

gSQL> 
SELECT REGEXP_SUBSTR( 'axxb', 'ax{3}b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxb', 'ax{3}b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxb', 'ax{3}b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
null    axxxb   null   
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'axxb', 'ax{3,}b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxb', 'ax{3,}b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxb', 'ax{3,}b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
null    axxxb   axxxxxb
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'axxb', 'ax{3,5}b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxb', 'ax{3,5}b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxb', 'ax{3,5}b' ) AS RESULT3,
       REGEXP_SUBSTR( 'axxxxxxxb', 'ax{3,5}b' ) AS RESULT4
  FROM dual;
RESULT1 RESULT2 RESULT3 RESULT4
------- ------- ------- -------
null    axxxb   axxxxxb null   
1 row selected.
• [: :]

gSQL> 
SELECT REGEXP_SUBSTR( 'abc123', '[[:alnum:]]+' ) AS RESULT1,
       REGEXP_SUBSTR( 'abc', '[[:alpha:]]+' ) AS RESULT2,
       REGEXP_SUBSTR( '123', '[[:digit:]]+' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
abc123  abc     123    
1 row selected.
• \d, \D, \w, \W, \s, \S

gSQL> 
SELECT REGEXP_SUBSTR( '123', '\d+' ) AS RESULT1,
       REGEXP_SUBSTR( 'abc', '\D+' ) AS RESULT2
  FROM dual;
RESULT1 RESULT2
------- -------
123     abc    
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'A_B_C', '\w+' ) AS RESULT1,
       REGEXP_SUBSTR( '+ @', '\W+' ) AS RESULT2
  FROM dual;
RESULT1 RESULT2
------- -------
A_B_C   + @    
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'a   d', 'a\s+d' ) AS RESULT1,
       REGEXP_SUBSTR( 'abc d', '\S+' ) AS RESULT2
  FROM dual;
RESULT1 RESULT2
------- -------
a   d   abc    
1 row selected.
• \A

gSQL> 
select * from t1;
C1             
---------------
Line1 : aaa xy1
Line2 : bbb xy2
Line3 : ccc xy3
1 row selected.

gSQL> 
SELECT REGEXP_COUNT( c1, '\ALine[[:digit:]]' ) AS RESULT_DEFAULT_MODE,
       REGEXP_COUNT( c1, '\ALine[[:digit:]]', 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
                  1                     1
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, '\ALine[[:digit:]]' ) AS RESULT_DEFAULT_MODE,
       REGEXP_SUBSTR( c1, '\ALine[[:digit:]]', 1, 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
Line1               Line1                
1 row selected.
• \Z

gSQL> 
SELECT c1 FROM t1;
C1 
---
abc    <-- First record abc\ndef 
def
abc    <-- Second record abc\ndef\n
def
   
2 rows selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, '.\Z' ) AS REGEXP_SUBSTR_RES 
  FROM t1;
REGEXP_SUBSTR_RES
-----------------
f                
f                
2 rows selected.
• \z

gSQL> 
SELECT * FROM t1;
C1             
---------------
Line1 : aaa xy1
Line2 : bbb xy2
Line3 : ccc xy3
1 row selected.

gSQL> 
SELECT REGEXP_COUNT( c1, '\w+\d\z' ) AS RESULT_DEFAULT_MODE,
       REGEXP_COUNT( c1, '\w+\d\z', 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
                  1                     1
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( c1, '\w+\d\z' ) AS RESULT_DEFAULT_MODE,
       REGEXP_SUBSTR( c1, '\w+\d\z', 1, 1, 'm' ) AS RESULT_MULTILINE_MODE
  FROM t1;
RESULT_DEFAULT_MODE RESULT_MULTILINE_MODE
------------------- ---------------------
xy3                 xy3                  
1 row selected.
• *?, +?, ??

gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'a\w*?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxbxb', 'a\w*?b' ) AS RESULT2
  FROM dual;
RESULT1 RESULT2
------- -------
ab      axxxb  
1 row selected.


gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'a\w+?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxbxb', 'a\w+?b' ) AS RESULT2
  FROM dual;
RESULT1 RESULT2
------- -------
null    axxxb  
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'ab', 'a\w??b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axbxb', 'a\w??b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxbxb', 'a\w?b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
ab      axb     null   
1 row selected.
• {n}?, {n,}?, {n,m}?

gSQL> 
SELECT REGEXP_SUBSTR( 'abxb', 'a\w{3}?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxbxbxb', 'a\w{3}?b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxbxbxb', 'a\w{3}?b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
null    axxxb   null   
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'abxb', 'a\w{3,}?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxbxb', 'a\w{3,}?b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxbxbxb', 'a\w{3,}?b' ) AS RESULT3
  FROM dual;
RESULT1 RESULT2 RESULT3
------- ------- -------
null    axxxb   axxxxxb
1 row selected.

gSQL> 
SELECT REGEXP_SUBSTR( 'abxb', 'a\w{3,5}?b' ) AS RESULT1,
       REGEXP_SUBSTR( 'axxxbxb', 'a\w{3,5}?b' ) AS RESULT2,
       REGEXP_SUBSTR( 'axxxxxbxbxb', 'a\w{3,5}?b' ) AS RESULT3,
       REGEXP_SUBSTR( 'axxxxxxxbxbxb', 'a\w{3,5}?b' ) AS RESULT4
  FROM dual;
RESULT1 RESULT2 RESULT3 RESULT4
------- ------- ------- -------
null    axxxb   axxxxxb null   
1 row selected.

JSON String Constructor

The JSON string constructor is a function that takes an SQL expression as an argument and generates a string in JSON format.

The JSON string constructor is categorized as follows:
• JSON value constructor
• JSON aggregate constructor
• JSON window constructor

JSON String Constructor

JSON value Constructor

The JSON value constructor is a single row function that generates one JSON string row for each input row.

The JSON value constructor is categorized as follows:

JSON aggregate Constructor

The JSON aggregate constructor is an aggregate function that generates a single JSON string row by aggregating the results.

The JSON aggregate constructor is categorized as follows:

JSON window Constructor

The JSON window constructor is a window function that generates JSON strings over a defined range of records using the OVER clause.

It differs from an aggregate function in that the number of result rows is determined by the groups within the window.

The JSON window constructor is categorized as follows:

JSON String

There are two types of JSON strings: JSON object strings and JSON array strings.

JSON Object String

A JSON object string is composed of consecutive key-value pairs enclosed in curly braces, and each key must be an SQL string.

{ key : value }
{ key : value, key : value, ... }

The following three functions return a JSON object string as the result.

JSON Array String

A JSON array string is composed of a sequence of values enclosed in square brackets.

[ value ]
[ value, value, ... ]

The following three functions return a JSON array string as the result.

JSON Structural Characters

There are six types of characters that make up a JSON string.

JSON structural character

Description

[

It is the square bracket used to start a JSON array string.

]

It is the square bracket used to close a JSON array string.

{

It is the curly brace used to start a JSON object string.

}

It is the curly brace used to close a JSON object string.

:

It is the character that separates keys.

,

It is the character that separates values.

These characters allow spaces before and after them.

JSON Escape Characters

The characters that are escaped within a JSON string are as follows.

Character

escape form

Description

"

\"

quotation mark

\

\\

back slash

CHR(8)

\b

backspace

CHR(12)

\f

form feed

CHR(10)

\n

new line feed

CHR(19)

\r

carriage return

CHR(9)

\t

tab

The following are examples of characters being escaped.

gSQL> SELECT JSON_OBJECT( 'quote' VALUE '"',
                          'backslash' VALUE '\',
                          'backspace' VALUE CHR(8),
                          'formfeed' VALUE CHR(12),
                          'newline' VALUE CHR(10),
                          'carriage_return' VALUE CHR(13),
                          'tab' VALUE CHR(9) ) AS escaped_json
       FROM dual;

ESCAPED_JSON                                                                    
-------------------------------------------------------------------------
{"quote":"\"","backslash":"\\","backspace":"\b","formfeed":"\f","newline":"\n","carriage_return":"\r","tab":"\t"}                                   

1 row selected.

The following is an example comparing an unescaped string and an escaped string.

--# Unescaped result
SELECT * FROM sample_table;

ID STRING_DATA             
-- ------------------------
 1 simple string           
 2 This is first sentence. 
   This is second sentence.
 3 He said, "Hello".       

3 rows selected.


--# Escaped result
SELECT JSON_OBJECT( 'ID'    VALUE id,
                    'DATA'  VALUE string_data ) AS json_string
  FROM sample_table;

JSON_STRING                                                        
-------------------------------------------------------------------
{"ID":1,"DATA":"simple string"}                                    
{"ID":2,"DATA":"This is first sentence.\nThis is second sentence."}
{"ID":3,"DATA":"He said, \"Hello\"."}                              

3 rows selected.

JSON Result Control Options

JSON Constructor Null Clause

These options control the output when the value argument of the JSON string constructor is null.

<JSON constructor null clause> ::=
    NULL ON NULL
  | ABSENT ON NULL
  | EMPTY STRING ON NULL

NULL ON NULL

When the value is null, it outputs JSON string null.

gSQL> SELECT JSON_OBJECT( name VALUE balances NULL ON NULL ) AS res_json_object
        FROM accounts;

RES_JSON_OBJECT
---------------
{"Alice":50000}
{"Bob":null}   
{"Chris":1000} 

3 rows selected.

gSQL> SELECT JSON_ARRAY( name, balances NULL ON NULL ) AS res_json_array
        FROM accounts;

RES_JSON_ARRAY 
---------------
["Alice",50000]
["Bob",null]   
["Chris",1000] 

3 rows selected.

ABSENT ON NULL

When the value is null, it is ignored and nothing is output.

gSQL> SELECT JSON_OBJECT( name VALUE balances ABSENT ON NULL ) AS res_json_object
        FROM accounts;

RES_JSON_OBJECT
---------------
{"Alice":50000}
{}             
{"Chris":1000} 

3 rows selected.

gSQL> SELECT JSON_ARRAY( name, balances ABSENT ON NULL ) AS res_json_array
        FROM accounts; 

RES_JSON_ARRAY 
---------------
["Alice",50000]
["Bob"]        
["Chris",1000] 

3 rows selected.

EMPTY STRING ON NULL

When the value is null, it outputs an empty string ("").

gSQL> SELECT JSON_OBJECT( name VALUE balances EMPTY STRING ON NULL ) AS res_json_object
        FROM accounts;

RES_JSON_OBJECT
---------------
{"Alice":50000}
{"Bob":""}     
{"Chris":1000} 

3 rows selected.

gSQL> SELECT JSON_ARRAY( name, balances EMPTY STRING ON NULL ) AS res_json_array
        FROM accounts;

RES_JSON_ARRAY 
---------------
["Alice",50000]
["Bob",""]     
["Chris",1000] 

3 rows selected.

JSON Key Uniqueness Constraint

The allowance of duplicate JSON object key fields can be configured.

<JSON key uniqueness constraint> ::=
    WITH UNIQUE [ KEYS ]
  | WITHOUT UNIQUE [ KEYS ]

WITH UNIQUE [ KEYS ]

Duplicate keys are not allowed within a JSON object. An error is returned if duplicates exist.

CREATE TABLE t1 ( key1 VARCHAR(3), data1 VARCHAR(3) );
INSERT INTO t1 VALUES ( 'K1', 'D1' );
INSERT INTO t1 VALUES ( 'K2', 'D2' );
INSERT INTO t1 VALUES ( 'K1', 'D3' );
COMMIT;

gSQL> SELECT JSON_OBJECTAGG( key1 VALUE data1 WITH UNIQUE KEYS ) AS result 
        FROM t1;

ERR-42000(13065): duplicate key names 'K1' in JSON object

WITHOUT UNIQUE [ KEYS ]

Duplicate keys are allowed within a JSON object.

gSQL> SELECT JSON_OBJECTAGG( key1 VALUE data1 WITHOUT UNIQUE KEYS ) AS result
      FROM t1;

RESULT                         
-------------------------------
{"K1":"D1","K2":"D2","K1":"D3"}

1 row selected.

JSON Array Aggregate Order By Clause

It generates the JSON_ARRAYAGG result by sorting values according to the sort specification list specified in the ORDER BY clause.

<JSON array aggregate order by clause> ::=
    ORDER BY <sort specification list>

<sort specification list> ::=
    <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
    <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
    <value expression>

<ordering specification> ::=
      ASC
    | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

The following is an example of sorting JSON array values using the JSON array aggregate order by clause.

CREATE TABLE t1 ( c1 int );
INSERT INTO t1 VALUES (3),(1),(null),(2);

gSQL> SELECT JSON_ARRAYAGG( c1 ) AS res_json_sort
       FROM t1;

RES_JSON_SORT
-------------
[3,1,2]     

gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 ) AS res_json_sort
       FROM t1;

RES_JSON_SORT
-------------
[1,2,3]
gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 ASC ) AS res_json_sort
        FROM t1;

RES_JSON_SORT
-------------
[1,2,3]                            

1 row selected.

gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 DESC ) AS res_json_sort
        FROM t1;

RES_JSON_SORT
-------------
[3,2,1]                             

1 row selected.
gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 NULL ON NULL ) AS res_json_sort
        FROM t1;

RES_JSON_SORT
-------------
[1,2,3,null]                                

1 row selected.

gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 NULLS FIRST NULL ON NULL ) AS res_json_sort
        FROM t1;

RES_JSON_SORT
-------------
[null,1,2,3]                                            

1 row selected.

gSQL> SELECT JSON_ARRAYAGG( c1 ORDER BY c1 NULLS LAST NULL ON NULL ) AS res_json_sort
        FROM t1;

RES_JSON_SORT
-------------
[1,2,3,null]                                           

1 row selected.

JSON Output Clause

The result type and output format of the string generated by the JSON string constructor can be controlled.

<JSON output clause> ::=
    RETURNING <string data type> [PRETTY]

<string data type> ::=
    CHAR(n)
  | VARCHAR(n)
  | LONG VARCHAR

The following is an example that specifies the result data type using the JSON output clause.

gSQL> SELECT JSON_OBJECT( name VALUE balances RETURNING VARCHAR(100) ) AS res_json_object
        FROM accounts;

RES_JSON_OBJECT
---------------
{"Alice":50000}
{"Bob":null}   
{"Chris":1000} 

3 rows selected.

The following is the result of applying the PRETTY option to the same example.

gSQL> SELECT JSON_OBJECT( name VALUE balances RETURNING VARCHAR(100) PRETTY ) AS res_json_object
        FROM accounts;

RES_JSON_OBJECT  
-----------------
{                
    "Alice":50000
}                
{                
    "Bob":null   
}                
{                
    "Chris":1000 
}                

3 rows selected.

JSON String Output Format

The string generated by the JSON string constructor is output as follows, depending on the SQL data type of the expression argument.

SQL data type of the expression

Output format

Number

Numeric

Boolean

Boolean

Character string

String

Binary string

String

Date/time

String

Interval

String

The output format of the resulting JSON string according to the SQL data type of each expression argument is as follows.

Number

The number type can represent all valid significant digits supported by numeric values.

gSQL> SELECT JSON_OBJECT( 'k_num' VALUE 100 ) AS result
        FROM dual;

RESULT       
-------------
{"k_num":100}

1 row selected.

gSQL> SELECT JSON_ARRAY( 100 ) AS result
        FROM dual;

RESULT
------
[100] 

1 row selected.

Boolean

gSQL> SELECT JSON_OBJECT( 'k_boolean' VALUE true ) AS result
        FROM dual;

RESULT            
------------------
{"k_boolean":true}

1 row selected.

gSQL> SELECT JSON_ARRAY( true ) AS result
        FROM dual;

RESULT
------
[true]

1 row selected.

Character String

gSQL> SELECT JSON_OBJECT( 'k_char' VALUE 'hello' ) AS result
        FROM dual;

RESULT            
------------------
{"k_char":"hello"}

1 row selected.

gSQL> SELECT JSON_ARRAY( 'hello' ) AS result
        FROM dual; 

RESULT   
---------
["hello"]

1 row selected.

Binary String

A binary string is represented as a hexadecimal string.

gSQL> SELECT JSON_OBJECT( 'k_binary' VALUE X'0011FF' ) AS result
        FROM dual;

RESULT               
---------------------
{"k_binary":"0011FF"}

1 row selected.

gSQL> SELECT JSON_ARRAY( X'0011FF' ) AS result
        FROM dual; 

RESULT    
----------
["0011FF"]

1 row selected.

Date/ Time

Date

The date type is output in the 'YYYY-MM-DDTHH:MM:SS' format.

gSQL> SELECT JSON_OBJECT( 'k_date' VALUE DATE '2025-05-05' ) AS result
        FROM dual;

RESULT                          
--------------------------------
{"k_date":"2025-05-05T00:00:00"}

1 row selected.

gSQL> SELECT JSON_ARRAY( DATE '2025-05-05' ) AS result
        FROM dual;

RESULT                 
-----------------------
["2025-05-05T00:00:00"]

1 row selected.

Time

The time type is output in the 'HH:MM:SS.FF6' format.

gSQL> SELECT JSON_OBJECT( 'k_time' VALUE TIME '15:30:59.999999' ) AS result
        FROM dual;

RESULT                      
----------------------------
{"k_time":"15:30:59.999999"}

1 row selected.

gSQL> SELECT JSON_ARRAY( TIME'15:30:59.999999' ) AS result
        FROM dual; 

RESULT             
-------------------
["15:30:59.999999"]

1 row selected.

Timestamp

The timestamp type is output in the 'YYYY-MM-DDTHH:MM:SS.FF6' format.

gSQL> SELECT JSON_OBJECT( 'k_timestamp' VALUE TIMESTAMP '2025-05-05 12:30:45 +09:00' ) AS result
        FROM dual;

RESULT                                            
--------------------------------------------------
{"k_timestamp":"2025-05-05T12:30:45.000000+09:00"}

1 row selected.

gSQL> SELECT JSON_ARRAY( TIMESTAMP '2025-05-05 12:30:45 +09:00' ) AS result
        FROM dual;

RESULT                              
------------------------------------
["2025-05-05T12:30:45.000000+09:00"]

1 row selected.

Interval

The interval type follows the duration format defined by ISO 8601.

Interval Year to Month

The Interval Year to Month type is represented in the 'P[n]Y[n]M' format.

gSQL> SELECT JSON_OBJECT( 'k_int_ytom' VALUE INTERVAL '3-6' YEAR TO MONTH ) AS result
        FROM dual; 

RESULT                
----------------------
{"k_int_ytom":"P3Y6M"}

1 row selected.

gSQL> SELECT JSON_ARRAY( INTERVAL '3-6' YEAR TO MONTH ) AS result
        FROM dual; 

RESULT   
---------
["P3Y6M"]

1 row selected.

gSQL> SELECT JSON_OBJECT( 'k_int_ytom' VALUE INTERVAL '0-6' YEAR TO MONTH ) AS result
        FROM dual; 

RESULT              
--------------------
{"k_int_ytom":"P6M"}

1 row selected.

gSQL> SELECT JSON_ARRAY( INTERVAL '0-6' YEAR TO MONTH ) AS result
        FROM dual; 

RESULT 
-------
["P6M"]

1 row selected.

gSQL> SELECT JSON_OBJECT( 'k_int_ytom' VALUE INTERVAL '0-0' YEAR TO MONTH ) AS result
        FROM dual;

RESULT              
--------------------
{"k_int_ytom":"P0Y"}

1 row selected.

gSQL> SELECT JSON_ARRAY( INTERVAL '0-0' YEAR TO MONTH ) AS result
        FROM dual; 

RESULT 
-------
["P0Y"]

1 row selected.

Interval Day to Second

The Interval Day to Second type is represented in the 'P[n]DT[n]H[n]M[n]S' format.

gSQL> SELECT JSON_OBJECT( 'k_int_dtos' VALUE INTERVAL '1 2:30:15.123' DAY TO SECOND ) AS result
        FROM dual;

RESULT                              
------------------------------------
{"k_int_dtos":"P1DT2H30M15.123000S"}

1 row selected.


gSQL> SELECT JSON_ARRAY( INTERVAL '1 2:30:15.123' DAY TO SECOND ) AS result
        FROM dual;

RESULT                 
-----------------------
["P1DT2H30M15.123000S"]

1 row selected.

gSQL> SELECT JSON_OBJECT( 'k_int_dtos' VALUE INTERVAL '0 00:30:00' DAY TO SECOND ) AS result
        FROM dual;

RESULT                
----------------------
{"k_int_dtos":"PT30M"}

1 row selected.

gSQL> SELECT JSON_ARRAY( INTERVAL '0 00:30:00' DAY TO SECOND ) AS result
        FROM dual;

RESULT   
---------
["PT30M"]

1 row selected.

gSQL> SELECT JSON_OBJECT( 'k_int_dtos' VALUE INTERVAL '0 00:00:00' DAY TO SECOND ) AS result
        FROM dual;

RESULT              
--------------------
{"k_int_dtos":"P0D"}

1 row selected.

gSQL> SELECT JSON_ARRAY( INTERVAL '0 00:00:00' DAY TO SECOND ) AS result
        FROM dual;

RESULT 
-------
["P0D"]

1 row selected.

Compatibility

The SQL standard compatibility for the JSON string constructor is as follows.

SQL standard compatibility for functions

Feature ID

Description

Availability

T811

Basic SQL/JSON constructor functions

O

T812

SQL/JSON: JSON_OBJECTAGG

O

T813

SQL/JSON: JSON_ARRAYAGG with ORDER BY

O

T814

Colon in JSON_OBJECT or JSON_OBJECTAGG

O

T830

Enforcing unique keys in SQL/JSON constructor functions

O