String Functions

Note: Using + should be avoided. It is an overloaded operator and can result in ambiguity when string columns and literals that contain only numeric values are mixed with numeric columns or literals.

Result type: Any character type

Returns the character equivalent of the value v1, which is an expression of any type.

Result type: INTEGER

Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.

Result type: VARCHAR or NCHAR

Returns the nth character or code point of c1. If n is larger than the length of the string, the result is a blank character.

Note: For Unicode (USC2) strings the value returned is the single USC2 character. For non-Unicode strings the values returned may be 1,2,3 or 4 bytes long depending on the character at offset n characters of c1.

SELECT CHAREXTRACT('company',4) returns 'p'

Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256.

SELECT CHR(65) returns 'A'.

SELECT CHR(345) returns 'Y'.

Result type: Any character or Unicode type, BYTE

Concatenates two or more strings.

SELECT CONCAT('1', '2', '3', '4') returns '1234'.

In earlier releases, to achieve the same result, you must nest the CONCAT function: SELECT CONCAT(CONCAT(CONCAT('1', '2'), '3'), '4').

The result size is the sum of the sizes of the arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.

Note: The concatenation of the BYTE data type cannot be used to create a table column implicitly in Vector tables; the result of such concatenation, however, can be inserted into a column with a character data type.

INSERT INTO test_concat SELECT CONCAT(BYTE('1'), 0x42, X'43', CAST('4' AS BYTE));

Result type: Any character or Unicode type

Converts all initial characters in c1 to upper case.

SELECT INITCAP('This is the final version (VERSION:5.a;6) of Leonard''s will')

returns:'This Is The Final Version (Version:5.A;6) Of Leonard's Will'

Calculates the Jaro-Winkler similarity between two VARCHAR strings. This is returned as a float value between 0 and 1, where 0.0 means no similarities and 1.0 means the strings are identical.

SELECT JARO_WINKLER('same','same') returns 1.0

Result type: Any byte, character, or Unicode type

Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1. len will be converted to a positive integer.

If len is not an integer value it will be rounded down equivalent to floor(len). If len is negative the result will be an empty string.

SELECT LEFT ('Company',4) returns 'Comp'

Result type: SMALLINT

If c1 is a fixed-length char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.

SELECT LENGTH ('Company') returns 7

Result type: INTEGER4

Calculates the Levenshtein distance between two VARCHAR strings. The Levenshtein distance between two strings is the minimum number of changes that need to be made to convert the source string into the target string.

Alias: LEVENSHTEIN_DISTANCE

SELECT LEVENSHTEIN('foo','fou') returns 1

Result type: SMALLINT

Returns the location of the first occurrence of c2 within c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table.

If c1 and c2 are different string data types, c2 is coerced into the c1 data type.

SELECT LOCATE ('Company', 'p') returns 4

Result type: Any character or Unicode type

Converts all upper case characters in c1 to lower case.

SELECT LOWER ('Company') returns 'company'

Result type: Any character type

Returns character expression of length n in which expr1 is prepended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the start of the result string.

SELECT LPAD ('Company',20, '-') returns '-------------Company'

Result type: Any character type

Returns character expression with leading blanks removed.

SELECT LTRIM (' Company') returns 'Company'

Result type: Any character string variable

Retains trailing blanks when placing a value in a varchar column. This function can be used only in an embedded SQL program. For more information, see the Embedded SQL Companion Guide.

Result type: INTEGER

Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.

octet(col1 VARCHAR(10), col2 CHAR(10));

INSERT INTO octet VALUES ('Company', 'Company')

SELECT OCTET_LENGTH (col1) returns 7

SELECT LENGTH (col1) returns 7

SELECT OCTET_LENGTH (col2) returns 10

SELECT LENGTH (col2) returns 7

Result type: SMALLINT

ANSI compliant version of LOCATE function. If a match exists, POSITION(c1 IN c2) is equal to LOCATE(c2, c1). If a match does not exist, POSITION returns 0, unlike LOCATE.

SELECT POSITION('p', 'Company') returns 4.

SELECT POSITION('z', 'Company') returns 0.

SELECT LOCATE('Company', 'p') returns 4 (same result as POSITION).

SELECT LOCATE('Company', 'z') returns 8 (one more than LENGTH(‘company’).

Result type: Any character type

Returns c1 (a character string) repeated n times.

SELECT REPEAT ('-',10) returns '----------'

SELECT REPEAT ('str',3) returns 'strstrstr'

Result type: Any character type

Returns character expression derived from expr1 in which all instances of expr2 have been replaced by expr3.

SELECT REPLACE('The priory was in the next town','priory','church')

returns: 'The church was in the next town'

Result type: Any character or Unicode type

Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1. len must be a positive integer.

SELECT RIGHT('The priory was in the next town',9)

Result type: Any character type

Returns character expression of length n in which expr1 is appended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the end of the result string.

SELECT RPAD('Company',12, '-') returns 'Company-----'

SELECT RPAD('Company',12, '-x') returns 'Company-x-x-'

Result type: Any character type

Returns character expression with trailing blanks removed.

SELECT LENGTH(RTRIM('Company ')) returns 7

Result type: Any character or Unicode type

Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.

SELECT SHIFT('Company',4) returns ' Com'

SELECT SHIFT('Company',‑4) returns 'any '

Result type: SMALLINT

Returns the declared size of c1 without removal of trailing blanks.

SELECT SIZE('Company') returns 7

Result type: Any character type

Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same SOUNDEX code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digits.

This standard (Russell) soundex function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than only the next strings in the index.

SELECT SOUNDEX('Company') returns 'C515'

SELECT SOUNDEX ('Company2012') returns 'C515'

Result type: VARCHAR

Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks.

This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.

SELECT SQUEEZE (' Company 2012 ') returns 'Company 2012'

Result type: VARCHAR, NVARCHAR

Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand.

If len is specified and is less than 1, SUBSTR returns NULL.

The loc parameter determines the start of the substring to be extracted. If loc is less than 0 the position is counted backwards from the end of c1; if loc is greater than 0 the position is counted from the beginning. If loc is 0 the start position is the first character.

After the start of the substring is determined, len characters are extracted. If len is not supplied, the rest of c1 is implied.

SELECT SUBSTR('Company 2012',9,2) returns '20'

SELECT SUBSTR('Company 2012',9) returns '2012'

SELECT SUBSTR('Company 2012',-9,4) returns 'pany'

Result type: VARCHAR, NVARCHAR

Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand. The result format is a varchar or nvarchar the size of c1. Unlike SUBSTR, loc and len must be positive values.

SELECT SUBSTRING('Company 2012',9,2) returns '20'

SELECT SUBSTRING('Company 2012',9) returns '2012'

SELECT SUBSTRING('Company 2012',-9,4) returns an empty string.

Result type: VARCHAR

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. This function performs a case-sensitive match when searching for delim.

Note: This function works only for data stored in tables created with the VECTORWISE or VECTORWISE_ROW storage structure.

Result type: VARCHAR

Returns c1 without trailing blanks. The result has the same length as c1.

Result type: Any character string variable

Returns c2 with all occurrences of c1—which can be only one character—removed from the beginning, end, or both, as specified. BOTH is the default. In the absence of c1, the space is assumed.

SELECT 'DEFAULT' , '['||TRIM(' Company ' ) ||']'

SELECT 'BOTH' , '['||TRIM(BOTH ' ' FROM ' Company ' ) ||']'

SELECT 'LEADING' , '['||TRIM(LEADING ' ' FROM ' Company ' ) ||']'

SELECT 'TRAILING' , '['||TRIM(TRAILING ' ' FROM ' Company ' ) ||']'

Result type: Any character type

Converts all lower case characters in c1 to upper case.

For the UTF8 character set, the character data is multi-byte string and the actual number of bytes for the data could be more than the number of characters. If the output buffer for any string operation is not sufficiently large to hold the multi-byte string, the result will be truncated at a character boundary.