Nisarg Upadhyay
CONCAT function example

Learn MySQL: MySQL String Functions

March 17, 2021 by

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

  1. String functions
  2. Control Flow functions
  3. Aggregate and Math functions
  4. 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:

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’:

Output:

ACSII function example

Example 2

The following query returns the ASCII value of ‘B

Output:

ACSII function example

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:

In the syntax, the str is the input value.

Example 1:

Output:

LENGTH function example

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:

In the syntax, the str is an input variable.

Example 1:

Output:

CHAR_LENGTH function example

Example 2:

Output:

CHAR_LENGTH function example

  • 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:

In the syntax, str_n is an input variable.

Example 1

Output:

CONCAT function example

Example 2:

Output:

CONCAT function example

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:

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

Example 1

Output

CONCAT_WS function example

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:

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

Example

Output:

RPAD  function example

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:

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

Example

Output:

LPAD  function example

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.

In the syntax, the str is the input value.

Example

Output:

UPPER  function example

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.

In the syntax, the str is the input value.

Example

Output:

LOWER  function example

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.

In the syntax, the str is the input value.

Example

Output:

LTRIM  function example

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.

In the syntax, the str is the input value.

Example

Output:

RTRIM  function example

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.

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

Syntax:

Example 1

Output:

LOCATE  function example

Example 2

Output:

LOCATE  function example

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

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

Example

Output:

LEFT function example

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:

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

Example

Output:

RIGHT function example

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:

In the syntax, str is an input string.

Example

Output:

REVERSE  function example

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

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

Example

Output:

SUBSTRING Example

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:

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

Output:

Substring() example

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:

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

Output:

SUBSTRING_INDEX  function example

Summary

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

Table of contents

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

Nisarg Upadhyay
MySQL

About 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

827 Views