Learn MySQL: MySQL String Functions

In this article, we are going to learn about different MySQL String functions. The MySQL String functions can be categorized into the following:

String functions
Control Flow functions
Aggregate and Math functions
Date-time functions

In this article, I am going to demonstrate and explain some common string functions. This function can be used to manipulate the output returned by the SELECT query. For the demonstration, I have installed MySQL Server 8.0 on my workstation. I am using the MySQL command line to demonstrate the string functions.

ASCII

The ASCII function returns the numeric value of the character. If you have entered a string as an input value, it returns the ASCII value of the string’s left most character. If the input string is NULL, then the function returns NULL. The syntax is the following:

1	SELECT ASCII(‘Str’)

In the syntax:

The str is an input character, number, or a string.

Example 1

The following query returns the numeric value of the ‘N’:

1	mysql> select ASCII('N');

Output:

Example 2

The following query returns the ASCII value of ‘B’

1	mysql> select ASCII('Bharti');

Output:

LENGTH()

The LENGTH() returns the length of the input string. The function accepts a string or a character as an input and returns the integer value.

Note: If the input string consists of six two-byte characters, then the function returns 12

Syntax:

1	Select length(‘str’)

In the syntax, the str is the input value.

Example 1:

1	mysql> Select LENGTH(‘Nirali’);

Output:

CHAR_LENGTH()

The CHARACTER_LENGTH() function is used to get the length of the string. The function accepts a string as an input and returns the integer value.

Note: if you are using a 6-character multi-byte string as an input value, then the char_length() function returns 6 as an output.

Syntax:

1	SELECT CHAR_LENGTH(str)

In the syntax, the str is an input variable.

Example 1:

1	mysql> Select CHAR_LENGTH(‘Nisarg’)as [String Length]

Output:

Example 2:

1	mysql> Select char_length(‘Nisarg Upadhyay’)As[String Length]

Output:

Note: The function returns 15 as an output because the space between two strings is considered as a character

CONCAT()

The CONCAT() string function is used to combine two or more input strings. The function accepts a binary and non-binary string as an input variable and returns the combined string.

Syntax:

1	Select CONCATE (str_1, str_2, str_3, …)

In the syntax, str_n is an input variable.

Example 1

1	mysql> SELECT CONCAT(‘NIRALI’,’UPADHYAY’)

Output:

Example 2:

1	mysql> SELECT CONCAT(‘NIRALI’,’.’,’UPADHYAY’)

Output:

CONCAT_WS()

The CONCATE_WS() string function is used to combine the string with the separator. The function accepts three arguments. The first argument is a separator. The data type of the separator must be a character. The second and third arguments are the input strings.

Syntax:

1	SELECT CONCAT(input_seprator, str_1,str_2..)

In the syntax, input_seprator is an input separator, str_1 and str_2 are the input strings’ values.

Example 1

1	mysql> SELECT CONCAT_WS(',','NIRALI','UPADHYAY') AS 'CONCAT STRING';

Output

RPAD()

The RPAD string function returns the string that is padded by the number of characters specified in the argument. The characters are the padded rightmost side of the input string. The function accepts three arguments. The first argument is the input string, the second is the number of the characters, and the third argument is a character.

Syntax:

1	mysql> Select RPAD (str, number_of_characters, input_character)

In the syntax, str is the string value, number_of_character is an integer value, and input_character is a character value.

Example

1	mysql> SELECT RPAD('NIRALI',10,'??') AS 'PADDED STRING';

Output:

LPAD()

The LPAD string function returns the string that is padded by the number of characters specified in the argument. The characters are padded left most side of the input string. The function accepts three arguments. The first argument is the input string, the second is the number of the characters, and the third argument is a character.

Syntax:

1	Select LPAD (str, number_of_characters, input_character)

In the syntax, str is the string value, number_of_character is an integer value, and input_character is a character value.

Example

1	mysql> SELECT LPAD('NIRALI',10,'??') AS 'PADDED STRING';

Output:

UPPER()

The UPPER() function converts the character of the input string into upper case. The function accepts an input string as an argument and returns the string converted n upper case.

1	SELECT UPPER(str)

In the syntax, the str is the input value.

Example

1	mysql> select UPPER('hello world');

Output:

LOWER()

The LOWER() function converts the character of the input string into a lower case. The function accepts an input string as an argument and returns the string converted n lower case.

1	SELECT LOWER(str)

In the syntax, the str is the input value.

Example

1	mysql> select LOWER('HELLO WORLD');

Output:

LTRIM()

The LTRIM() function trims the leading space character from the input string. The function accepts an input string as an argument, trims the leading space character, and returns the output.

1	SELECT LTRIM(str)

In the syntax, the str is the input value.

Example

1	mysql> select LTRIM(' HELLO WORLD') as 'Trimmed string';

Output:

RTRIM()

The RTRIM() function trims the rightmost space character from the input string. The function accepts an input string as an argument, trims the rightmost space character, and returns the output.

1	SELECT RTRIM(str)

In the syntax, the str is the input value.

Example

1	mysql> select RTRIM('HELLO WORLD ') as 'Trimmed string';

Output:

LOCATE()

The LOCATE() function is used to return the position of the substring within an input string. If the input string has multiple occurrences of the same substring, then the function retrieves the position of the string’s first occurrence. The function accepts two arguments.

The first argument is the substring whose position you want to retrieve
The second argument is an input string

Syntax:

1	SELECT LOCATE(substring, str)

Example 1

1	mysql> select locate ('upadhyay', 'nisarg dixit upadhyay') as 'locate string';

Output:

Example 2

1	mysql> select locate ('dixit', 'nisarg dixit dixit upadhyay') as 'multiple occurrence of string';

Output:

LEFT()

The LEFT() function returns the leftmost characters specified in the argument. The function accepts two arguments. The first argument is the input string, and the second argument is the number of characters you want to return from the input string.

Syntax

1	Select LEFT(str, chars)

In the syntax, the value of the str is the input string, and chars are a number of the characters.

Example

1	mysql> select LEFT('dixit', 2) as 'Right Chars';

Output:

RIGHT()

The RIGHT() function returns the rightmost characters specified in the argument. The function accepts two arguments. The first argument is the input string, and the second argument is the number of characters you want to return from the input string.

Syntax:

1	Select RIGHT(str, chars)

In the syntax, the value of the str is the input string, and chars are a number of the characters.

Example

1	mysql> select RIGHT('dixit', 2) as 'Right Chars';

Output:

REVERSE()

The REVERSE() function is used to reverses the order of the input string. The function accepts an input string and returns the reversed input string

Syntax:

1	Select REVERSE(str)

In the syntax, str is an input string.

Example

1	mysql> select REVERSE('dixit') as 'Reverse string';

Output:

SUBSTRING()

The SUBSTRING() function has two variants. The first variant is SUBSTRING(str, position). The function returns the substring, starting from the position specified in the position argument.

Syntax

1	SELECT SUBSTRING(str, position)

In the syntax, the str is the input string, and the position is the starting position of the substring.

Example

1	mysql> select substring('nisarg',4);

Output:

The second variant is SUBSTRING(str, start, total). In this variant, the function returns the substring, starting from the position specified in the start argument, and returns the number of characters specified in the tot argument.

Syntax:

1	SELECT SUBSTRING(str, start, total)

In the syntax, str is the input string, the start is the starting position of the substring, and the total is the number of characters returned in the substring.

Example

1	mysql> select substring('nisarg',2,4);

Output:

SUBSTRING_INDEX()

The SUBSTRING_INDEX() string function returns the substring before the character specified in the input string. The function accepts three arguments. The first argument is an input string. The second argument is the delimiter, and the third argument is the number of occurrences of the delimiter in an input string.

Syntax:

1	SELECT SUBSTRING(str, delim, n)

In the syntax, str is an input string, delim is a delimiter that you want to search, and chars are the number of the delimiter occurrences. The datatype of the chars is an integer, and it can be a positive or negative number. If the value of the chars argument is a positive number, then the function returns all characters from the right up to the chars number of occurrences of the delimiter. If the value of the chars argument is negative, then the function returns all characters from the left up to the chars number of occurrences of the delimiter.

Example

1	mysql> select substring_index('nisarg@upadhyay','@',1) as 'Substring';

Output:

Summary

In this article, we have learned some common string functions of MySQL like Concat(), Substring(), Length(), Locate(), etc.

Learn MySQL: Querying data from MySQL server using the SELECT statement

Learn MySQL: What is pagination

Learn MySQL: Sorting and Filtering data in a table

Learn MySQL: Add data in tables using the INSERT statement

Learn MySQL: Create and drop temp tables

Learn MySQL: Delete and Update Statements

Learn MySQL: The Basics of MySQL Stored Procedures

Learn MySQL: The Basics of MySQL Views

Learn MySQL: An overview of MySQL Binary Logs

Learn MySQL: An overview of the mysqlbinlog utility

Learn MySQL: Run multiple instances of MySQL Server on Windows 10

Learn MySQL: MySQL String Functions

Learn MySQL: Control Flow functions

Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive

Learn MySQL: MySQL Copy table

Author
Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022

SQLShack

Learn MySQL: MySQL String Functions