Other DB2 string functions: CHAR, HEX, DIGITS and SOUNDEX

Map of the remaining string helpers

CHAR, VARCHAR, and VARGRAPHIC are conversion functions that happen to return strings. HEX and DIGITS format values as character digits. SOUNDEX and DIFFERENCE implement a phonetic algorithm. CHR, ASCII_CHR, and ASCII_STR deal with code points and ASCII representations. All are scalar, all return null if an argument is null, and all live in SYSIBM.

CHAR — fixed-length character representation

CHAR returns a fixed-length character string representation of its argument. The argument can be a number, a character or graphic string, a datetime value, or a ROWID. Optional length and string-unit arguments apply when you convert a string to a shorter or longer CHAR.

1
2
3
4
5
6
7
8
9
10
11
CHAR(integer-expression)
CHAR(decimal-expression)
CHAR(decimal-expression, decimal-character)
CHAR(floating-expression)
CHAR(string-expression, length)
CHAR(datetime-expression)
CHAR(datetime-expression, ISO)
CHAR(datetime-expression, USA)
CHAR(datetime-expression, EUR)
CHAR(datetime-expression, JIS)
CHAR(datetime-expression, LOCAL)

Integer CHAR is a left-blank-padded character form of the number, including a leading minus for negatives. Decimal CHAR uses a decimal-character (default depends on the decimal point option; typically a period in many shops) and does not drop the scale. Datetime CHAR is how you force a DATE, TIME, or TIMESTAMP into a specific display format:

• ISO — international: dates YYYY-MM-DD, times HH.MM.SS
• USA — MM/DD/YYYY dates, HH:MM AM/PM times
• EUR — DD.MM.YYYY dates, HH.MM.SS times
• JIS — Gregorian with hyphen dates and colon times (IBM Japan / JIS)
• LOCAL — the installation date/time exit format (LOCAL DATE LENGTH / LOCAL TIME LENGTH on DSNTIP4)

1
2
3
4
5
SELECT CHAR(HIREDATE, ISO) AS ISO_DATE,
       CHAR(HIREDATE, USA) AS USA_DATE,
       CHAR(SALARY) AS SAL_CHAR
  FROM DSN8C10.EMP
 FETCH FIRST 5 ROWS ONLY;

When CHAR shortens a string, extra characters on the right are truncated. When it lengthens a string, Db2 pads with blanks. Length for CHAR of a string is an integer constant from 1 to 255. CODEUNITS16, CODEUNITS32, and OCTETS can qualify that length for Unicode data. Bit data cannot use CODEUNITS16/32.

CHAR of a decimal can surprise COBOL eyes: the result includes the decimal point and sign, so it is not a PIC 9 image. Use DIGITS when you need only digits.

VARCHAR and VARGRAPHIC

VARCHAR is the varying-length cousin of CHAR. It converts the same families of arguments (integers, decimals, floats, DECFLOAT, character and graphic strings, datetime, ROWID) to VARCHAR. Optional length (up to 32764 in the specified string units) sets the length attribute of the result. If you omit length for a string argument, the result length attribute matches the source.

1
2
3
4
5
SELECT VARCHAR(EMPNO) AS EMP_VC,
       VARCHAR(LASTNAME, 10) AS NAME10,
       VARCHAR(HIREDATE) AS HIRE_VC
  FROM DSN8C10.EMP
 FETCH FIRST 5 ROWS ONLY;

VARCHAR of a datetime uses the default string representation for that type (installation default unless you go through CHAR with an explicit format, or VARCHAR_FORMAT / TIMESTAMP_FORMAT on newer function sets). Prefer explicit CHAR(date, ISO) when the format must be stable across subsystems.

VARGRAPHIC returns a varying-length graphic string. Use it when the target is GRAPHIC / VARGRAPHIC / DBCLOB-oriented data (EBCDIC DBCS or Unicode UTF-16 graphic). Converting EBCDIC character data to graphic requires a valid conversion path; mismatched CCSIDs raise errors rather than silently mojibake if no converter exists. For Unicode tables, VARGRAPHIC is often UTF-16 (CCSID 1200) data.

IBM’s portability note applies here too: CAST(x AS VARCHAR(20)) is easier to move between Db2 family products than remembering every CHAR/VARCHAR overload. On z/OS you will still meet CHAR(date, USA) constantly in existing programs.

HEX — hexadecimal dump of a value

HEX(expression) returns a character string of hexadecimal digits (0–9, A–F) that represent the argument. Each byte becomes two hex characters. It is the first tool to reach for when “the column looks blank” or “this CHAR FOR BIT DATA is garbage.”

1
2
3
4
5
6
SELECT LASTNAME,
       HEX(LASTNAME) AS NAME_HEX,
       HEX(SALARY) AS SAL_HEX,
       HEX(HIREDATE) AS DATE_HEX
  FROM DSN8C10.EMP
 FETCH FIRST 3 ROWS ONLY;

• Character EBCDIC — HEX('A') is often C1, not 41. ASCII 41 is a different encoding. That single fact explains many “wrong hex in the textbook” arguments.
• DECIMAL — packed decimal bytes, including the sign nibble (D negative, C or F positive, depending on the value).
• DATE — four packed bytes (YYYY MM DD), so eight hex digits, not the 10-character display.
• VARCHAR — includes the length prefix bytes in some contexts when you HEX a host variable, but HEX of a VARCHAR column expression is the character bytes of the value; know whether you are dumping a column or a COBOL layout.

HEX is a diagnostic, not a storage type. Do not HEX a column in a WHERE clause as a substitute for a proper predicate: you disable indexes and compare display digits instead of values. Convert back only when you truly need to interpret bytes (for example comparing FOR BIT DATA).

DIGITS — unsigned digit string

DIGITS(numeric-expression) returns a character string of the absolute value of a number with leading zeros and without a sign or decimal point. Scale digits are included in the character result; the decimal point is not.

1
2
3
4
SELECT DIGITS(SMALLINT(12)) AS D5,
       DIGITS(INTEGER(12)) AS D10,
       DIGITS(DECIMAL(-123.45, 5, 2)) AS D5S2
  FROM SYSIBM.SYSDUMMY1;

SMALLINT yields CHAR(5), INTEGER CHAR(10), BIGINT CHAR(19), DECIMAL CHAR(precision). DIGITS(DECIMAL(-123.45, 5, 2)) is '12345'. The minus sign is gone; the point is gone; precision is preserved with leading zeros if needed (12 in SMALLINT is '00012').

That shape is why shops build keys with DIGITS(BRANCH) CONCAT DIGITS(ACCT). CHAR(12) would be a blank-padded '12' and would sort as a string differently from '00012'. DIGITS keeps a fixed width of digits.

SOUNDEX and DIFFERENCE — phonetic matching

SOUNDEX(expression) returns a four-character code: the first character is the first letter of the string (uppercased in the algorithm), and the next three characters are digits derived from remaining letters. Vowels and some consonants are dropped according to the classic American Soundex rules. The result type is CHAR(4).

DIFFERENCE(expression-1, expression-2) applies SOUNDEX to both arguments and returns an INTEGER from 0 to 4 that counts how many corresponding characters of the two codes agree. 4 is the best match (identical Soundex). 0 is no similarity on that scale. Numeric arguments are cast to character first.

1
2
3
4
5
SELECT DIFFERENCE('CONSTRAINT', 'CONSTANT') AS DIFF4,
       SOUNDEX('CONSTRAINT') AS SX1,
       SOUNDEX('CONSTANT') AS SX2,
       DIFFERENCE('CONSTRAINT', 'CONTRITE') AS DIFF2
  FROM SYSIBM.SYSDUMMY1;

IBM’s sample: CONSTRAINT and CONSTANT both Soundex to C523, so DIFFERENCE is 4. CONSTRAINT versus CONTRITE is C523 versus C536, DIFFERENCE 2. A search such as DIFFERENCE(LASTNAME, 'SMITH') = 4 will include SMYTHE and similar spellings, and also unrelated collisions. Do not unique-index a Soundex code and expect one person.

Non-English names, leading prefixes, and very short strings are weak spots. For serious matching, shops use dedicated search products or additional keys; SOUNDEX remains a useful built-in first cut.

CHR, ASCII_CHR and ASCII_STR

CHR

CHR(integer) returns the UTF-8 character for that Unicode code point. The integer must identify a valid character. This is the function to build a character from a Unicode scalar value in Unicode SQL.

ASCII_CHR

ASCII_CHR(integer) returns the character that has that ASCII code point. The result carries an ASCII CCSID. Integer 65 is ASCII 'A' (byte 41 hex), which is not EBCDIC 'A' (C1 hex). If your session encoding is EBCDIC, Db2 converts the ASCII character into the application encoding when it displays or assigns the result, so you still see A — but HEX of an ASCII_CHR result before conversion is the ASCII byte.

ASCII_STR

ASCII_STR(string) (also ASCIISTR) returns an ASCII representation of a string. Characters that are not in ASCII are typically emitted as a backslash and four hex digits (Unicode style escapes). It is a way to inspect or transport a Unicode string through an ASCII channel without losing code points.

1
2
3
4
SELECT CHR(65) AS UNICODE_A,
       ASCII_CHR(65) AS ASCII_A,
       ASCII_STR('ABC') AS ASCII_ABC
  FROM SYSIBM.SYSDUMMY1;

Related siblings you will see in manuals: EBCDIC_CHR, EBCDIC_STR, UNICODE_STR. They mirror the same idea for other encodings. Pick the function whose name matches the encoding you intend, then confirm CCSID with HEX if the result looks “almost right.”

Conversion functions versus CAST

CHAR, VARCHAR, and VARGRAPHIC overlap the CAST specification and the INTEGER/DECIMAL family of conversion functions. Rules of thumb:

• Need a datetime format name (ISO, USA, EUR, JIS, LOCAL) — CHAR(datetime, format)
• Need a portable, obvious target type — CAST(x AS VARCHAR(40))
• Need digit-only fixed width from a number — DIGITS
• Need to see bytes — HEX

Function resolution still applies: CHAR is overloaded. The data type of the argument picks the CHAR function. A distinct type usually needs CAST to a built-in type first. Unqualified CHAR searches the SQL path; built-in SYSIBM functions are found unless a user function with the same signature sits earlier on the path.

Explain It Like I'm Five

CHAR and VARCHAR are costume changes: the number 12 puts on a text costume so it can sit next to names. DIGITS is a costume that is only digit magnets, including extra zero magnets on the left so every number is the same width. HEX is X-ray vision that shows the secret byte stickers on the back of each magnet. SOUNDEX is a nickname stamp: SMITH and SMYTHE get the same stamp so you can find them together. CHR is “give me the magnet that has this number on its back.” ASCII_CHR asks for the American-English numbering of magnets, which is a different numbering than the mainframe’s EBCDIC stickers.

Exercises

1. Compare CHAR(SALARY), VARCHAR(SALARY), and DIGITS(SALARY) on DSN8C10.EMP. Describe padding, decimal points, and signs in each result.
2. HEX an EBCDIC literal 'A' and explain why it is not 41.
3. Write a predicate that finds last names that Soundex-match 'JOHNSON' with DIFFERENCE = 4. List two reasons this can return the wrong people.
4. Format HIREDATE as USA and as ISO using CHAR. Why might VARCHAR(HIREDATE) differ across two subsystems?
5. Build a 10-digit character account key from DECIMAL(7,0) branch and INTEGER account using DIGITS and CONCAT, preserving leading zeros.