Skip to main content
This following table describes the functions and operators for examining and manipulating string values (CHAR and VARCHAR data types). See Character types. Except where noted, these functions and operators are declared to accept and return type VARCHAR. Notes:
  1. Text comparison is case-sensitive (e.g., ‘Regatta’ <> ’REGATTA’ <> ‘regatta’ and ‘A’ < ‘B’ < ‘a’ < ‘b’).
  2. Values of type CHAR are converted to VARCHAR before the function or operator is applied, resulting in stripping any trailing spaces in the character value.
FunctionDescription and CommentsExample
string || stringConcatenate of two strings. See also CONCAT(). See note 1.'This in an ' || 'example'
=> 'This in an example'
CONCAT(string, string [, ...])Concatenate two or more strings. See note 2.CONCAT('Hello', ' it', ' is me')
=> 'Hello it is me'
LOWER(string)Convert a string to all lowercaseLOWER('Hello')
=> 'hello'
UPPER(string)Convert a string to all uppercaseUPPER('Hello')
=> 'HELLO'
POSITION(sub_string IN string)Return the first starting index of the specified substring within the string, or zero if it’s not present. The return type is INTPOSITION('o' IN 'Hello')
=> 5
SUBSTRING(string, start [, count] )

SUBSTRING([FROM start] [FOR count])		Extracts a substring from a given string.
See note 3		SUBSTRING(‘hello’, 2, 3) => ‘ell’
SUBSTRING(‘hello’, 2) => ‘ello’
SUBSTRING(‘hello’ FROM 2 FOR 3) => ‘ell’
SUBSTRING(‘hello’ FROM 2) => ‘ello’
SUBSTRING ‘hello’ FOR 3) => ‘hel’
TRIM( [LEADING | TRAILING | BOTH] { [trim_chars FROM] | [FROM] } string)

TRIM([LEADING | TRAILING | BOTH] [FROM] string [, trim_chars])		Remove the longest string containing only characters in trim_chars (space by default), from either the front or the back of the input string, or both (the default). See note 4.TRIM(LEADING ‘xyz’ FROM ‘zxxzhello’)
=> ‘hello’
TRIM(BOTH ‘xyz’ FROM ‘yxthis is mezy’)
=> ‘this is me’
TRIM(‘ hello ’)
=> ‘hello’
TRIM(‘__hello__’, ‘_’)
=> ‘hello’
BTRIM(STRING [, trim_chars])Alias of TRIM() with BOTHBTRIM('xthis_is_mez', 'xz')
=> 'this_is_me'
BTRIM(' hello ') = TRIM(' hello ')
=> true
LTRIM(STRING [, trim_chars])Alias of TRIM() with LEADINGLTRIM('xthis_is_mez', 'xz')
=> 'this_is_mez'
RTRIM(STRING [, trim_chars])Alias of TRIM() with TRAILINGRTRIM('xthis_is_mez', 'xz')
=> 'xthis_is_me'
LEFT(string, count)Return the first count characters of a string. If count is negative, return all but last count characters.LEFT('This is me', 8)
=> 'This is '
LEFT('This is me', -8)
=> 'Th'
RIGHT(string, count)Return the last count characters of a string. If count is negative, return all but the first count characters.RIGHT('This is me', 8)
=> 'is is me'
RIGHT('This is me', -8)
=> 'me'
LENGTH(string)Return the number of characters in a string. The return type is INT.LENGTH('This is me')
=> 10
LPAD(string, length [, fill_string])Extend a string to a given length by prepending the fill_string (a space by default). See note 5.LPAD('Hello', 10, '-')
=> '-----Hello'
LPAD('Hello', 10)
=> ' Hello'
RPAD(string, length [, fill_string])Extend a string to a given length by appending the fill_string (a space by default). See note 5.RPAD('Hello', 10, '12')
=> 'Hello12121'
REPEAT(string, count)Repeats a string the specified number of timesREPEAT('Hello', 3)
=> 'HelloHelloHello'
REPLACE(string, from, to)Replace all occurrences of substring from with substring toREPLACE('Hello, this is me', 'me', 'you')
=> 'Hello, this is you'
REVERSE(string)Reverse the order of the characters in a stringREVERSE('Hello')
=> 'olleH'
string [NOT] LIKE pattern [ESCAPE escape-char]Match a string expression by a pattern. The match is case sensitive. Returns BOOLEAN. See note 6.'Regatta' LIKE 'Rega%'
=> true
'Regatta' LIKE '_e_a%a'
=> true
‘20% discount’ LIKE ’20!%’ ESCAPE ‘!’
=> false
‘20% discount’ LIKE ’20!%%’ ESCAPE ‘!’
=> true
Notes:
  1. The string concatenation operator (||) accepts non-string input, so long as at least one input is of string type. Using the operator with NULL as one of the operator inputs, returns NULL. For example: SELECT 'Hello' || NULL || ' this is me'; returns NULL
  2. CONCAT():
    • All values are implicitly converted to strings
    • NULL arguments are ignored. For example: SELECT CONCAT('Hello', NULL, ' this is me'); Return: ‘Hello this is me’
  3. SUBSTRING(string, start [, count]) receives start position and optional count. If count is omitted, the extracted substring is until the end of the input string.
    SUBSTRING(string [FROM start] [FOR count]) – both FROM and FOR are optional but at least one of them should be included. If FROM is omitted, start from the beginning of the input string. If FOR count is omitted, extract until the end of the input string.
    If the FROM argument is less than 1, the returned substring begins from position 1 but the difference is subtracted from the count. For example, SELECT SUBSTRING(‘hello’, FROM -1 FOR 3) returns ‘h’.
  4. TRIM() – In the first form, ‘FROM’ is mandatory if trim_chars is included and optional otherwise. In the second form it’s optional.
  5. LPAD() (RPAD()): If the input string is longer than the requested length then it is truncated on the right (left).
  6. LIKE and NOT LIKE:
    1. String match is case sensitive
    2. Escape character should be a single character.
    3. Pattern special characters:
      • An underscore (_) stands for any single character
      • A percent sign (%) stands for any sequence of zero or more characters
      • If the pattern does not contain percent signs (%) or underscores (_), it becomes a test for exact match of two strings.