Question & Answer
Question
Does DB2 support the string delimiter with double characters?
Cause
In some cases, it is required to store strings with many special characters e.g. an OS command. The default DB2 string delimiter is double quote("). When the LOAD command is used to load data from a .del file which may contain such string:
ls |grep -i "?aaaa%&$*@#!^~`';,"
it may be difficult to find a string delimiter.
Answer
DB2 only supports single-byte character as a string delimiter.
One of the solutions is to find a very rarely used character such as 0x1F which is the ASCII unit separator. For example, use the following LOAD command by specifying string delimiter 0x1F:
$db2 "load from sample.del of del replace into t2 modified by chardelx1F"
Note that the valid code point range of string delimiter is :
- For MBCS data, the valid range for the delimiters is 0x00 - 0x3F inclusive.
- For SBCS or UTF-8 data, the valid range for the delimiters is 0x00 - 0x7F inclusive.
- For EBCDIC MBCS data, the valid range for the delimiters is 0x00 - 0x3F inclusive with the exception that the delimiters cannot be the SHIFT-OUT (0x0E) or the SHIFT-IN (0x0F) characters.
If users cannot find any single character in the valid range from the above, they may consider the following method:
Since double quote is the default string delimiter, if the source string in .del file contains double quote e.g
ls -l *.dat|grep -i "aaa"
consider using Double Character Delimiters so that the source string is changed to
"ls -l *.dat|grep -i ""aaa""".
By default, for character-based fields of a DEL file, any instance of the character delimiter found within the field is represented by double character delimiters.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21474249