38.5.15 #SET NULL (specifies a null value display format) : Hitachi Advanced Database Command Reference

(1) Function

Specifies the character string to be displayed when the retrieval result is a null value.

The default value of the character string to be displayed when the retrieval result is a null value is the value specified in the ADBSQLNULLCHAR environment variable. If the ADBSQLNULLCHAR environment variable is not specified, the default value is an asterisk (*).

To Page Top

(2) Specification format

#SET NULL {[character-string-to-be-displayed-for-null-value]
             |REPEAT fill-character-to-be-displayed-for-null-value
             |DELETE};
 
  character-string-to-be-displayed-for-null-value::=character-string-lateral
  fill-character-to-be-displayed-for-null-value::=character-string-lateral

To Page Top

(3) Explanation of options

character-string-to-be-displayed-for-null-value:

Specifies the character string to be displayed when the retrieval result is a null value.

The following rules apply when specifying this option:

Specify a character string enclosed in single quotation marks ( ' ).

Example: 'NULL'
A character string (0 to 32 bytes) can be specified.
If this option is omitted or if a 0-byte character string is specified for it, spaces are displayed when the retrieval result is a null value.
If a specified character string includes one or more single quotation marks (' ), use two single quotation marks ('') to express each single quotation mark ('). For example, if you want the character string N'ULL displayed for a null value, specify as follows:

'N''ULL'

REPEAT fill-character-to-be-displayed-for-null-value:

Specifies the character to be repeated (fill character) to be displayed when the retrieval result is a null value. A character string with the specified fill character, repeated up to the maximum width of the retrieval results that can be displayed, is displayed when the retrieval result is a null value.

The following rules apply when specifying this option:

Specify a fill character enclosed in single quotation marks ( ' ).

Example: ':'
Set the length of the fill character to be specified to 1 byte.
To use a single quotation mark ( ' ) as a fill character, specify as follows.

''''

DELETE

Returns the character string to be displayed to its default value when the retrieval result is a null value.

To Page Top

(4) Execution examples

Displaying the default value for a null value

SELECT CAST(NULL AS CHAR(4)) FROM "T1";     ...1
 
 EXP0001_NO_NAME
 ---------------
            ****                            ...2

Explanation:

Executes an SQL statement without executing #SET NULL.
If the retrieval result is a null value, the value specified in the ADBSQLNULLCHAR environment variable is displayed. If the ADBSQLNULLCHAR environment variable is not specified, asterisks (*) are displayed.

Displaying a character string for a null value

#SET NULL 'NULL';                            ...1
SELECT CAST(NULL AS CHAR(4)) FROM "T1";      ...2
 
 EXP0001_NO_NAME
 ---------------
            NULL                             ...3

Explanation:

Specifies NULL for the character string to be displayed when the retrieval result is a null value, and executes #SET NULL.
Executes an SQL statement.
If the retrieval result is a null value, NULL is displayed.

Displaying spaces for a null value

#SET NULL '';                               ...1
SELECT CAST(NULL AS CHAR(4)) FROM "T1";     ...2
 
 EXP0001_NO_NAME
 ---------------
                                            ...3

Explanation:

Specifies a 0-byte character string for the character string to be displayed when the retrieval result is a null value, and executes #SET NULL.
Executes an SQL statement.
If the retrieval result is a null value, spaces are displayed.

Displaying colons (:) for a null value (with the fill character specified)

#SET NULL REPEAT ':';                       ...1
SELECT CAST(NULL AS CHAR(4)) FROM "T1";     ...2
 
 EXP0001_NO_NAME
 ---------------
            ::::                            ...3

Explanation:

Specifies the colon (:) for the fill character to be displayed when the retrieval result is a null value, and executes #SET NULL.
Executes an SQL statement.
When the retrieval result is a null value, the colon (:) is repeatedly displayed up to the maximum width of the retrieval results that can be displayed.

To Page Top

(5) Notes

If multi-byte characters are specified in the character string to be displayed when the retrieval result is a null value, the character string might be displayed correctly.
If the character string to be displayed when the retrieval result is a null value exceeds the width that can be displayed for retrieval results, only the portion of the retrieval result that can be displayed out of the specified character string is displayed. If multi-byte characters are specified in the character string to be displayed when the retrieval result is a null value, and if the end of the width that can be displayed in the retrieval result falls in the middle of a multi-byte character, only the part of the character string up to the preceding character is displayed.

To Page Top