String functions

You can use these string-manipulation functions on file names and literal values.

Important tips:

You must enclose strings in single-quotation marks.
You can include a single-quotation mark in a string by using two single-quotation marks. For example, 'a''b' represents the string a'b.

CHAR(expr[, length])

Returns a fixed-length character string representation of its expr argument, where:

expr: Can be any data type.
length: If present, must be a literal, integer value.

The resulting type is CHAR or VARCHAR, depending upon the particular function called.

The string that CHAR returns is padded with blanks to fill the length of the string. If length is not specified, it defaults to a value that depends on the type of the argument (expr).

Note: The maximum length of a CHAR (fixed length string) value is 255 bytes. The result of evaluating an SQL expression whose result is type CHAR may be truncated to this maximum length.

CONCAT(x,y)

Concatenates strings x and y.

HEX(x)

Converts an integer x into hexadecimal format.

LENGTH(x)

Determines the length of the data type of string x.

LOWER(x)

Converts string x into lowercase.

REGEX(String,'Pattern')

Returns TRUE if the pattern matches, FALSE if it does not. Pattern is a Posix extended regular expression.

Note: The policy SQL parser normally performs M4 macro preprocessing with square brackets set as the quote characters. Therefore, it is recommended that you add an extra set of square brackets around your REGEX pattern string; for example:

...WHERE REGEX(name,['^[a-z]*$'])  /* only accept lowercase alphabetic file names */

The following SQL expression:

NOT REGEX(STRING_VALUE,['^[^z]*$|^[^y]*$|^[^x]*$|[abc]'])

can be used to test if STRING_VALUE contains all of the characters x, y, and z, in any order, and none of the characters a, b, or c.

REGEXREPLACE(string,pattern,result-prototype-string)

Returns a character string as result-prototype-string with occurrences of \i (where i is 0 through 9) replaced by the substrings of the original string that match the i^th parenthesis delimited parts of the pattern string. For example:

REGEXREPLACE('speechless',['([^aeiou]*)([aeiou]*)(.*)'],['last=\3. middle=\2. first=\1.'])

returns the following:

'last=chless. middle=ee. first=sp.'

When pattern does not match string, REGEXREPLACE returns the value NULL.

When a \0 is specified in the result-prototype-string, it is replaced by the substring of string that matches the entire pattern.

SUBSTR(x,y,z)

Extracts a portion of string x, starting at position y, optionally for z characters (otherwise to the end of the string). This is the short form of SUBSTRING. If y is a negative number, the starting position is counted from the end of the string; for example, SUBSTR('ABCDEFGH',-3,2) == 'FG'.

Note: Do not confuse SUBSTR with substr. substr is an m4 built-in macro function.

SUBSTRING(x FROM y FOR z)

Extracts a portion of string x, starting at position y, optionally for z characters (otherwise to the end of the string).

UPPER(x)

Converts the string x into uppercase.

VARCHAR(expr [, length ])

Returns a varying-length character string representation of a character string, date/time value, or numeric value, where:

expr: Can be any data type.
length: If present, must be a literal, integer value.

The resulting type is CHAR or VARCHAR, depending upon the particular function called. Unlike CHAR, the string that the VARCHAR function returns is not padded with blanks.

Note: The maximum length of a VARCHAR(variable length string) value is 8192 bytes. The result of evaluating an SQL expression whose result is type VARCHAR may be truncated to this maximum length.