String Functions
10 methodsStandard SQL string functions (MySQL / PostgreSQL compatible unless noted).
CONCAT(str1, str2, ...)Concatenate strings. NULL arguments are ignored (MySQL) or propagate (ANSI).
Parameters
| Name | Type | Description |
|---|---|---|
| str1 | string | First string. |
| str2 | string | Second string. |
Returns
string
Example
SELECT CONCAT('Hello', ', ', 'World');
-- 'Hello, World'
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;SUBSTRING(str FROM pos FOR len)Return a substring starting at position pos (1-based) of length len. Also written SUBSTR.
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
| pos | int | Start position (1-based). |
| len | int | Length. |
Returns
string
Example
SELECT SUBSTRING('hello world' FROM 1 FOR 5);
-- 'hello'
SELECT SUBSTRING('hello world' FROM 7);
-- 'world'LENGTH(str)Return the length of the string in characters (CHAR_LENGTH) or bytes (OCTET_LENGTH in MySQL).
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
Returns
int
Example
SELECT LENGTH('hello'); -- 5
SELECT CHAR_LENGTH('café'); -- 4 (characters)
SELECT OCTET_LENGTH('café'); -- 5 (bytes in UTF-8)UPPER(str)Convert the string to uppercase.
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
Returns
string
Example
SELECT UPPER('hello'); -- 'HELLO'
SELECT UPPER('café'); -- 'CAFÉ'LOWER(str)Convert the string to lowercase.
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
Returns
string
Example
SELECT LOWER('HELLO'); -- 'hello'
SELECT LOWER('CAFÉ'); -- 'café'REPLACE(str, from_str, to_str)Return str with all occurrences of from_str replaced by to_str.
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
| from_str | string | Substring to replace. |
| to_str | string | Replacement. |
Returns
string
Example
SELECT REPLACE('a-b-c', '-', '_');
-- 'a_b_c'
SELECT REPLACE('hello', 'l', 'L');
-- 'heLLo'TRIM([LEADING|TRAILING|BOTH] [chars] FROM str)Remove leading and/or trailing characters from str. Default removes spaces from both ends.
Parameters
| Name | Type | Description |
|---|---|---|
| chars | string | Characters to remove (default space). |
| str | string | Source string. |
Returns
string
Example
SELECT TRIM(' hi '); -- 'hi'
SELECT TRIM(LEADING '0' FROM '00123'); -- '123'
SELECT TRIM(BOTH 'x' FROM 'xxhixx'); -- 'hi'LEFT(str, n)Return the leftmost n characters of str. (MySQL / SQL Server / PostgreSQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
| n | int | Number of characters. |
Returns
string
Example
SELECT LEFT('hello', 3); -- 'hel'
SELECT LEFT('abc', 10); -- 'abc'RIGHT(str, n)Return the rightmost n characters of str. (MySQL / SQL Server / PostgreSQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| str | string | Source string. |
| n | int | Number of characters. |
Returns
string
Example
SELECT RIGHT('hello', 3); -- 'llo'
SELECT RIGHT('abc', 10); -- 'abc'CHARINDEX(substr, str)Return the 1-based starting position of substr in str, or 0 if not found. (SQL Server; LOCATE/INSTR in MySQL/PostgreSQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| substr | string | Substring to find. |
| str | string | Source string. |
Returns
int
Example
-- SQL Server
SELECT CHARINDEX('lo', 'hello'); -- 4
SELECT CHARINDEX('z', 'hello'); -- 0
-- MySQL alternative:
SELECT LOCATE('lo', 'hello'); -- 4