SQL String Functions: A Complete Overview

A review of all core SQL string functions, including their definitions and examples.

SQL string functions manipulate string (including text and alphanumeric) values. Also known as text functions in SQL, string functions take one or more string values as an argument and return a modified string value.

Check out this interactive course on Standard SQL Functions if you want more hands-on practice than this article includes. It provides 211 exercises on numeric SQL functions, string or text functions, date and time functions, and aggregate functions. Complete the exercises to review and consolidate your knowledge, then put it to a test by taking the final quiz.

Right, let’s move on to our overview of SQL’s string functions.

List of SQL String Functions

Important: In SQL, we enclose string values in single quotes like this: 'this is a string'. But what if we want to include a single quote character in the string value? Check out our cookbook How to Escape Single Quotes in SQL to learn how to escape special characters in SQL strings.

Note: SQL string functions may differ between database engines. Below we list functions available in popular databases, including PostgreSQL, MySQL, and SQL Server.

Function	Description	Further reading
|| (Concatenate)	→ Takes two or more strings. ← Concatenates (combines) them and returns the resulting string.	* How to concatenate strings in SQL * How to concatenate string and NULL values in SQL Server
< (Less than)  > (Greater than)  <= (Less than or equal to) >= (Greater than or equal to)  = (Equals)  <> , != (Does not equal)	→ Takes two strings. ← Compares them and returns a Boolean value.	* How to compare two strings in SQL * How to order strings alphabetically in SQL
LIKE	→ Takes one string and one regex template. ← Compares them and returns a Boolean value.	* How to use LIKE in SQL
CONCAT()	→ Takes two or more strings. ← Concatenates them and returns the resulting string.	* How to concatenate strings in SQL * How to concatenate string and NULL values in SQL Server
CONCAT_WS()	→ Takes a separator value and two or more strings. ← Concatenates them with the separator value in between and returns the resulting string.	* How to concatenate strings in SQL * How to concatenate string and NULL values in SQL Server
LEFT()	→ Takes one string and one integer (x). ← Returns x characters, starting from the left (e.g. LEFT(5) returns the first 5 characters in the string).
LENGTH() (SQL Server: LEN())	→ Takes one string. ← Returns the number of characters in the string.	* How to check the length of a string in SQL
LOWER()	→ Takes one string. ← Returns this string with all characters in lower case.	* How to convert a string to lowercase in SQL
LPAD() (SQL Server equivalents available)	→ Takes a string, an integer, and another string. ← Pads the first string value (starting from the left) with repetitions of the second string value to make the length equal to the integer.
LTRIM()	→ Takes one string. ← Trims all white spaces from the left and returns the resulting string.
POSITION() (SQL Server: CHARINDEX())	→ Takes two strings. ← Returns the position number at which the first string is present in the second string.
REPEAT() (SQL Server: REPLICATE())	→ Takes a string and an integer (x). ← Returns a string that contains x string values (e.g. REPEAT('a', 3) returns 'aaa').
REPLACE()	→ Takes three strings. ← Returns the first string where the value of the second string is replaced by the third string (e.g. REPLACE('Hello world', 'Hello', 'Hi') returns 'Hi World').	* How to replace part of a string in MySQL * How to replace part of a string in SQL * How to replace part of a string in T-SQL
REVERSE()	→ Takes one string. ← Returns the reversed value of the string.
RIGHT()	→ Takes one string and one integer (x). ← Returns x characters from the right (e.g. RIGHT(5) returns the last 5 characters in the string).
RPAD() (SQL Server equivalents available)	→ Takes a string, an integer, and another string. ← Pads the first string (starting from the right) with repetitions of the second string value to make the length equal to the integer.
RTRIM()	→ Takes one string. ← Trims all white spaces from the right and returns the resulting string.
SPLIT_PART() (MySQL:  SUBSTRING_INDEX() SQL Server: STRING_SPLIT())	→ Takes two strings and an integer. ← Splits the first string based on a separator provided in the second string and returns one part as indicated by the integer.	* How to split a string in MySQL * How to split a string in Oracle * How to split a string in PostgreSQL * How to split a string in SQL Server
STRING_AGG() (MySQL: GROUP_CONCAT())	→ Takes one column of a string type and one string. ← Returns all values from the column, separated by the string.
SUBSTRING()	→ Takes one string and two integers (x and y). ← Returns a substring consisting of the characters between the positions x and y.	* How to extract a substring from a string in Oracle or SQLite * How to extract substrings in PostgreSQL and MySQL * How to extract a substring from a string in T-SQL * A complete guide to working with substrings in SQL * The SQL substring function in 5 examples
TRANSLATE() (Not available in MySQL)	→ Takes three strings. ← Returns the first string where each value from the 2nd string is replaced with each value from the 3rd string. TRANSLATE('Hello World', 'ol', 'ek') will replace each occurrence of 'o' with 'e' and each occurrence of 'l' with 'k', resulting in 'Hekke Werkd'.
TRIM()	→ Takes one string. ← Returns the string with leading and trailing white spaces removed.	* How to trim strings in SQL
UPPER()	→ Takes one string. ← Returns this string with all characters in upper case.	* How to convert a string to uppercase in SQL

SQL String Functions - Examples

The following sections present examples for each function.

Concatenation with ||

This operator takes two or more strings and returns the concatenated value.

SELECT 'Hello' || ' World' AS concatenated;

concatenated
Hello World

Note that if any operand is null, then the result is null:

concatenated
null

Read these articles to learn more about:

• How to concatenate strings in SQL.
• How to concatenate string and NULL values in SQL Server.

Comparison with <, >, <=, >=, =, <>, !=

These operators take two strings, compare them, and return a Boolean value. A Boolean value indicates True (1) or False (0).

SELECT 'ABC' < 'XYZ' AS compare;

compare
1

Because ABC comes before XYZ in the alphabet, this expression evaluates to True. In SQL, text values are evaluated based on their alphabetical order. You can also see if string expressions match (or not):

SELECT 'ABC' = 'XYZ' AS compare;

compare
0

Read these articles to learn more about:

• How to compare two strings in SQL.
• How to order strings alphabetically in SQL.

LIKE

This operator takes one string and one template using regex, compares them, and returns a Boolean value. We’ll use three regex templates:

• A% means “starts with A and has any number of characters following”. Add, ACT, and AbE would all match this regex.
• A_C means “starts with A, has one character, and then ends with C”. AAC, abc, aDc, and alC would all match this regex.
• %h means “ends with h”. With, twenty-fifth, and aah would all match this regex.

Important: Although most major SQL dialects are case-insensitive, regex patterns are case-sensitive; ‘A’ and “a” are two different characters in regex.

SELECT 'ABC' LIKE 'A%' AS compare;

compare
1

SELECT 'ABC' LIKE 'A_C' AS compare;

compare
1

SELECT 'Hello' LIKE '%H' AS compare;

compare
0

Check out this article to learn how to use LIKE in SQL.

CONCAT()

This function takes two or more strings, concatenates them (i.e. combines them into one string), and returns the resulting string.

SELECT CONCAT('Hello', ' World', '!') AS concat;

concat
Hello World!

Note that this function ignores null values – unlike the || operator:

SELECT CONCAT('Hello', null, '!') AS concat;

concat
Hello!

Read these articles to learn more about:

• How to concatenate strings in SQL.
• How to concatenate strings and NULL values in SQL Server.

CONCAT_WS()

This function takes a separator value and two or more strings, concatenates them with the separator value in between, and returns the resulting string.

SELECT CONCAT_WS('_','Hello', 'World') AS concat_ws;

concat_ws
Hello_World

Note that this function ignores null values, as opposed to the || operator.

SELECT CONCAT_WS('_','Hello', null, 'World') AS concat_ws;

concat_ws
Hello_World

Read these articles to learn more about:

• How to concatenate strings in SQL.
• How to concatenate string and NULL values in SQL Server.

LEFT()

This function takes one string and one integer. Starting from the beginning (the left), it returns as many characters as indicated by the integer.

SELECT LEFT('Hello', 2) AS left;

left
He

LENGTH()

This function takes one string and returns the number of characters in that string.

SELECT LENGTH('Hello') AS length;

length
5

Note that SQL Server uses LEN() instead of LENGTH().

Check out this article to learn how to check the length of a string in SQL.

LOWER()

This function takes one string and returns this string with all characters in lower case.

SELECT LOWER('Hello') AS lower;

lower
hello

Check out this article to learn how to convert a string to lowercase in SQL.

LPAD()

This function takes a string, an integer, and another string. Starting from the beginning (the left), it pads the first string value with repetitions of the second string value to make the length equal to the integer.

SELECT LPAD('Hello', 9, 'ABC') AS lpad;

lpad
ABCAHello

Note that SQL Server does not provide this function. Check out some equivalents available in SQL Server.

LTRIM()

This function takes one string, trims all white spaces from the left, and returns the resulting string.

SELECT LTRIM('   Hello World   ') as ltrim;

ltrim
Hello World

Note that any whitespaces on the right side of the string are not affected.

POSITION()

This function takes two strings and returns the position number at which the first string is present in the second string.

SELECT POSITION('Wo' IN 'Hello World') as position;

position
7

Note that SQL Server uses CHARINDEX() instead of POSITION().

REPEAT()

This function takes a string and an integer. It returns a string that contains as many repetitions of the first string as indicated by the integer.

SELECT REPEAT('Hi', 3) as repeat;

repeat
HiHiHi

Note that SQL Server uses REPLICATE() instead of REPEAT().

REPLACE()

This function takes three strings and returns the first string where the value indicated by the second string is replaced by the third string.

SELECT REPLACE('Hello World', 'Hello', 'Hi') as replace;

replace
Hi World

Read these articles to learn more about:

• How to replace part of a string in MySQL.
• How to replace part of a string in SQL.
• How to replace part of a string in T-SQL.

REVERSE()

This function takes one string and returns the reversed value of the string.

SELECT REVERSE('Hello') as reverse;

reverse
olleH

RIGHT()

This function takes one string and one integer. Starting from the end of the string (the right), it returns as many characters as indicated by the integer.

right
lo

RPAD()

This function takes a string, an integer, and another string. Starting from the end of the string (the right), it pads the first string with repetitions of the second string to make the length equal to the integer.

SELECT RPAD('Hello', 9, 'ABC') AS rpad;

rpad
HelloABCA

Note that SQL Server does not provide this function. Check out some equivalents available in SQL Server.

RTRIM()

This function takes one string, trims all white spaces from the end, and returns the resulting string.

SELECT RTRIM('   Hello World   ') as rtrim;

rtrim
Hello World

Note that any whitespaces at the front (left) of the string are not affected.

SPLIT_PART()

This function takes two strings and an integer. It splits the first string based on the separator provided in the second string and returns the part indicated by the integer. The following code splits ‘Hello World’ by the space and returns the first part of the split string:

SELECT SPLIT_PART('Hello World', ' ', 1) AS part;

part
Hello

Let’s do the same thing, but this time we’ll change the integer to 2. This will return the second part of the string:

SELECT SPLIT_PART('Hello World', ' ', 2) AS part;

part
World

Note that MySQL uses SUBSTRING_INDEX() instead of SPLIT_PART().

Note that SQL Server uses STRING_SPLIT() instead of SPLIT_PART().

Read these articles to learn more about:

• How to split a string in MySQL.
• How to split a string in Oracle.
• How to split a string in PostgreSQL.
• How to split a string in SQL Server.

STRING_AGG()

This function takes one string-type column and one string. It returns the concatenation of all values from the column; each value is separated from the others by the string. This is an aggregate function that can be used with GROUP BY.

This is the fruits table that we’ll use in this example:

name	amount
Apple	1
Kiwi	2
Banana	3

SELECT STRING_AGG(name, ';') AS string_agg
FROM fruits;

string_agg
Apple;Kiwi;Banana

Note that MySQL uses GROUP_CONCAT() instead of STRING_AGG().

SUBSTRING()

This function takes one string and two integers. It returns a substring consisting of the characters between the positions indicated by the integers.

SELECT SUBSTRING('Hello World', 2, 5) AS substring;

substring
ello

Read these articles to learn more about:

• How to extract a substring from a string in Oracle or SQLite.
• How to extract substrings in PostgreSQL and MySQL.
• How to extract a substring from a string in T-SQL.
• A complete guide to working with substrings in SQL.
• The SQL substring function in 5 examples.

TRANSLATE()

This function takes three strings and returns the first string where the values indicated by the second string are replaced by the values indicated by the third string.

SELECT TRANSLATE('Hello World', 'Ho', 'he') as translate;

This example takes a given string value and replaces H with h and o with e.

translate
helle Werld

SELECT TRANSLATE('abc def', 'ad', 'xy') as translate;

The above example takes a given string value and replaces a with x and d with y.

translate
xbc yef

Note that MySQL does not provide this function. Check out some equivalents available in MySQL.

TRIM()

This function takes one string and returns the string with leading and trailing white spaces removed.

SELECT TRIM('   Hello World   ') AS trim;

trim
Hello World

Check out this article to learn how to trim strings in SQL.

UPPER()

This function takes one string and returns this string with all characters in upper case.

SELECT UPPER('Hello') AS upper;

upper
HELLO

Check out this article to learn how to convert a string to uppercase in SQL.

More SQL Functions

SQL provides many different functions – numeric functions, aggregate functions, string functions, date and time functions, and more. For more information on them, check out our SQL cookbook and our blog articles.

Also, check out this free Standard SQL Functions Cheat Sheet. You can download it, print it, and keep it handy as you work and learn. Once again, we encourage you to check out our course on Standard SQL Functions if you really want to get some hands-on practice. Happy learning!