AS400 String Scalar Functions

The Following are the AS/400 String Scalar Functions

Char(X,F)
Returns the string-representation of a date, time or timestamp value, X. F is the format, (which is optional), and can be: USA, ISO, JUL or any defined system format.

CHARACTER_LENGTH(X)
Retuns a long integer representing the length of X if X is a character string. Lengths of VARCHAR is the actual length of the string, not the max length of the Varchar. The length includes any trailing blanks.

CONCAT(X,Y)
Returtns a string that is the result of concatinating X||Y

Example: Select concat(FirstName,LastName) from Emp

LEFT(S,L)
Returns the leftmost L characters from string S. IF L is bigger than S, S is padded with blanks.

LENGTH(X)
Returns the length of X.

LTRIM(X)
Left trims the string X, (removes beginning blanks).

RTRIM(X)
Trims trailing blanks from string X.

STRIP(S,Dir,Chr)
Strings character Chr from string S in direction Dir. Valid directions are Both, Leading or Trailing.

SUBSTR(S,Str,Len) or SUBSTRING(S,Str,Len)
Returns a substring beginning at position Str and continuing to Len from string S.

TRANSLATE(X,C1,C2)
Replaces occurrences of C1 with C2 in string X.

TRIM(X)
Removes leading and trailing blanks from string X.

UCASE(X) or UPPER(X)
Returns X as an all caps string.

VARCHAR(X,L)
Returns X as a varchar string of max imum length L.

VARGRAPHIC(X,L)
Returns X as a vargraphic of max length L