String Functions

String functions perform various character manipulations. They operate on character strings.

Function	Example	Description	Syntax
ASCII	ASCII('a')	Converts a single character string to its corresponding ASCII code, between 0 and 255. If the character expression evaluates to multiple characters, the ASCII code corresponding to the first character in the expression is returned. expr is any expression that evaluates to a character string.	ASCII(expr)
BIT_LENGTH	BIT_LENGTH('abcdef')	Returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length (equal to 16 bits). expr is any expression that evaluates to a character string.	BIT_LENGTH(expr)
CHAR	CHAR(35)	Converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code. expr is any expression that evaluates to a numerical value between 0 and 255.	CHAR(expr)
CHAR_LENGTH	CHAR_LENGTH(Customer_Name)	Returns the length, in number of characters, of a specified string. Leading and trailing blanks aren’t counted in the length of the string. expr is any expression that evaluates to a character string.	CHAR_LENGTH(expr)
CONCAT	SELECT DISTINCT CONCAT ('abc', 'def') FROM employee	Concatenates two character strings. exprs are expressions that evaluate to character strings, separated by commas. You must use raw data, not formatted data, with CONCAT.	CONCAT(expr1, expr2)
INSERT	SELECT INSERT('123456', 2, 3, 'abcd') FROM table	Inserts a specified character string into a specified location in another character string. expr1 is any expression that evaluates to a character string. Identifies the target character string. integer1 is any positive integer that represents the number of characters from the beginning of the target string where the second string is to be inserted. integer2 is any positive integer that represents the number of characters in the target string to be replaced by the second string. expr2 is any expression that evaluates to a character string. Identifies the character string to be inserted into the target string.	INSERT(expr1, integer1, integer2, expr2)
LEFT	SELECT LEFT('123456', 3) FROM table	Returns a specified number of characters from the left of a string. expr is any expression that evaluates to a character string integer is any positive integer that represents the number of characters from the left of the string to return.	LEFT(expr, integer)
LENGTH	LENGTH(Customer_Name)	Returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters. expr is any expression that evaluates to a character string.	LENGTH(expr)
LOCATE	LOCATE('d' 'abcdef')	Returns the numeric position of a character string in another character string. If the character string isn’t found in the string being searched, the function returns a value of 0. expr1 is any expression that evaluates to a character string. Identifies the string for which to search. expr2 is any expression that evaluates to a character string. Identifies the string to be searched.	LOCATE(expr1, expr2)
LOCATEN	LOCATEN('d' 'abcdef', 3)	Like LOCATE, returns the numeric position of a character string in another character string. LOCATEN includes an integer argument that enables you to specify a starting position to begin the search. expr1 is any expression that evaluates to a character string. Identifies the string for which to search. expr2 is any expression that evaluates to a character string. Identifies the string to be searched. integer is any positive (nonzero) integer that represents the starting position to begin to look for the character string.	LOCATEN(expr1, expr2, integer)
LOWER	LOWER(Customer_Name)	Converts a character string to lowercase. expr is any expression that evaluates to a character string.	LOWER(expr)
OCTET_LENGTH	OCTET_LENGTH('abcdef')	Returns the number of bytes of a specified string. expr is any expression that evaluates to a character string.	OCTET_LENGTH(expr)
POSITION	POSITION('d', 'abcdef')	Returns the numeric position of strExpr1 in a character expression. If strExpr1 isn’t found, the function returns 0. expr1 is any expression that evaluates to a character string. Identifies the string to search for in the target string. For example, "d". expr2 is any expression that evaluates to a character string. Identifies the target string to be searched. For example, "abcdef".	POSITION(expr1, expr2)
REPEAT	REPEAT('abc', 4)	Repeats a specified expression n times. expr is any expression that evaluates to a character string integer is any positive integer that represents the number of times to repeat the character string.	REPEAT(expr, integer)
REPLACE	REPLACE('abcd1234', '123', 'zz')	Replaces one or more characters from a specified character expression with one or more other characters. expr1 is any expression that evaluates to a character string. This is the string in which characters are to be replaced. expr2 is any expression that evaluates to a character string. This second string identifies the characters from the first string that are to be replaced. expr3 is any expression that evaluates to a character string. This third string specifies the characters to substitute into the first string.	REPLACE(expr1, expr2, expr3)
RIGHT	SELECT RIGHT('123456', 3) FROM table	Returns a specified number of characters from the right of a string. expr is any expression that evaluates to a character string. integer is any positive integer that represents the number of characters from the right of the string to return.	RIGHT(expr, integer)
SPACE	SPACE(2)	Inserts blank spaces. integer is any positive integer that indicates the number of spaces to insert.	SPACE(expr)
SUBSTRING	SUBSTRING('abcdef' FROM 2)	Creates a new string starting from a fixed number of characters into the original string. expr is any expression that evaluates to a character string. startPos is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.	SUBSTRING([SourceString] FROM [StartPostition])
SUBSTRINGN	SUBSTRING('abcdef' FROM 2 FOR 3)	Like SUBSTRING, creates a new string starting from a fixed number of characters into the original string. SUBSTRINGN includes an integer argument that enables you to specify the length of the new string, in number of characters. expr is any expression that evaluates to a character string. startPos is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.	SUBSTRING(expr FROM startPos FOR length)
TrimBoth	Trim(BOTH '_' FROM '_abcdef_')	Strips specified leading and trailing characters from a character string. char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default. expr is any expression that evaluates to a character string.	TRIM(BOTH char FROM expr)
TRIMLEADING	TRIM(LEADING '_' FROM '_abcdef')	Strips specified leading characters from a character string. char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default. expr is any expression that evaluates to a character string.	TRIM(LEADING char FROM expr)
TRIMTRAILING	TRIM(TRAILING '_' FROM 'abcdef_')	Strips specified trailing characters from a character string. char is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default. expr is any expression that evaluates to a character string.	TRIM(TRAILING char FROM expr)
UPPER	UPPER(Customer_Name)	Converts a character string to uppercase. expr is any expression that evaluates to a character string.	UPPER(expr)