String Functions and Operators

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:

Text comparison is case-sensitive (e.g: ‘Regatta’ <> ’REGATTA’ <> ‘regatta’ and ‘A’ < ‘B’ < ‘a’ < ‘b’).
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.

Function	Description and Comments	Example
`STRING		STRING`	Concatenate of two strings. See also CONCAT(). See notes.	‘This in an ’ \|\| ‘example’ => This in an example
`CONCAT(STRING [, STRING [, ...]])`	Concatenation of all the string arguments. See notes.	`CONCAT('Hello', ' it', ' is me')` => Hello it is me
`LOWER(STRING)`	Converts the string to all lowercase	`LOWER('Hello')` => hello
`UPPER(STRING)`	Converts the string to all uppercase	`UPPER('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. Returns an INT	`POSITION('o' IN 'Hello')` => 5
`SUBSTRING(STRING start_int [, count_int] [FROM start_int] [FOR count_int])`	Extracts the substring of string starting at the start_int position if that is specified, and stopping after count_int characters if that is specified. If start_int is not specified - default to 1. If count_int is not specified - default is until the end of the string. If start_int is less than 1, the difference (1 - start_int) is subtracted from count_int and the result starts from position 1	`SUBSTRING('123456789' FROM 4 FOR 4)` => 4567 `SUBSTRING('123456789' FOR 2)` => 12 `SUBSTRING('123456789' FROM 4)` => 456789 `SUBSTRING('123456789' FROM -1 FOR 4)` => 12
`SUBSTRING(STRING start\_int FOR count\_int)`	Alias of SUBSTRING	`SUBSTRING('My example', 3, 5)` => exam
`TRIM( [LEADING	TRAILING	BOTH] [trim_chars] FROM STRING)`	Removes the trim_chars characters (a space by default) from the start or the end, or both sides of the string (BOTH is the default)	`TRIM(LEADING 'xx' FROM 'xxhello')` => hello `TRIM(FROM ' hello ')` => hello (no spaces) `TRIM(BOTH 'xyz' FROM 'xthis_is_mezy')` => this_is_me
`BTRIM(STRING [, trim_chars])`	Alias of TRIM with BOTH	`BTRIM('this_is_mez', 'xz')` => this_is_me
`LTRIM(STRING [, trim_chars])`	Alias of TRIM with LEADING	`LTRIM('xthis_is_mex', 'x')` => this_is_mex
`RTRIM(STRING [, trim_chars])`	Alias of TRIM with TRAILING	`RTRIM('xthis_is_mex', 'x')` => xthis_is_me
`LEFT(STRING, n_int)`	Returns the first n_int characters in the string, or when n_int is negative, returns all but last n_int characters	`LEFT('This is me', 8)` => This is `LEFT('This is me', -8)` => Th
`RIGHT(STRING, n_int)`	Returns the last n_int characters in the string, or when n_int is negative, returns all but last n_int characters	`RIGHT('This is me', 8)` => is me `RIGHT('This is me', -8)` => me
`LENGTH(STRING)`	Returns the number of characters in the string. Returns an INT. See notes.	`LENGTH('This is me')` => 10
`LPAD(STRING, length_int [, fill_string])`	Extends the string to length_int length by prepending the fill_string (a space by default). Returns an INT. See notes.	`LPAD('Hello', 10, '-')` => -----Hello `LPAD('Hello', 10)` => Hello (5 spaces)
`RPAD(STRING, length_int [, fill_string])`	Extends the string to length_int length by appending the fill_string (a space by default). See notes.	`RPAD('Hello', 10, '-')` => Hello-----
`REPEAT(STRING, n_int)`	Repeats string the specified n_int times	`REPEAT('Hello', 3)` => Hello_Hello_Hello
`REPLACE(STRING, from_text, to_text)`	Replaces all occurrences in string of substring from_text with substring to_text	`REPLACE('Hello this is me', 'me', 'you')` => Hello this is you
`REVERSE(STRING)`	Reverses the order of the characters in the string	`REVERSE('Hello')` => olleH
`string [NOT] LIKE pattern [ESCAPE escape-char]`	Matches (or not) string expressions by patterns. The match is case sensitive. Returns BOOLEAN. See notes.	`'This is me' LIKE 'This%'` => true `'This is me' LIKE '%is%'` => true `'20% discount' LIKE '20!%' ESCAPE '!'` => true

Notes:

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'; Return: NULL
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’
LPAD() (RPAD()): If the string is already longer than the requested length_int then it is truncated on the right (left).
LIKE and NOT LIKE: a . String match is case sensitive b . escape character should be a single character. c. Possible patterns:
- An underscore (_): stands for a single character
- A percent sign (%) stands for sequence of zero or more characters
- If the pattern does not contain percent signs (%) or underscores (_), then the [NOT] LIKE operator matches the full string.

SQL Specification

SQL Syntax Conventions

Data Types

SQL Functions and Operators

Data Definition Language (DDL) Commands

Data Manipulation Language Commands

Transaction Control Language Commands

Data Query Language

Hints

Managing Users

System Monitoring

System Management

Appendices

String Functions and Operators