MySQL String Function

The string function is used to maintain and operate string values. This function modifies the string data as per function. We can do the concatenation, conversion, removal and replacing the string data by using the string function.

MySQL String Function Syntax

MySQL string function syntax gives below.

SELECT string_function("write string here..");

MySQL string function with position syntax shows below.

SELECT string_function("write string here..", position);

MySQL string function with several value syntax shows below.

SELECT string_function("string1", "string2");

MySQL String Functions

The following table shows string functions and its description.

Function	Description
ASCII	The ASCII() function shows the ASCII value of the given character.
CHAR_LENGTH	This function displays the character length. The string contains the character and returns the length of the character, including spaces.
CONCAT	The concat() function combines the two or more expressions of the data.
CONCAT_WS	The concat_ws() function is part of the MySQL string function that separates the two or more expressions of the data after concatenation.
FIELD	This function displays the index position of a value in a list.
FIND_IN_SET	The char_length() function displays the position of a value in a list.
INSERT	The INSERT function inserts a string at the required position with a given number of characters.
INSTR	The INSTR function displays the position of the first occurrence of a string with the other strings.
LCASE	The LCASE function uses to convert a string to lower-case. This function returns a string in lower-case.
UCASE	The UCASE function converts a string to upper-case and returns in capital string.
UPPER	The UPPER function is used to convert and shows a given string to the upper-case.
LEFT	The LEFT function extracts several characters from a left string.
LENGTH	The LENGTH function displays the length of a string in bytes format.
LOCATE	The LOCATE function displays the position of the first circumstance of a substring.
LOWER	The LOWER function is used to convert a string to lower-case.
LTRIM	The LTRIM function removes the initial spaces of the string.
MID	The MID function extracts a substring of a string at any position.
POSITION	The POSITION function displays the position of the first occurrence of a substring in a string.
REPEAT	The REPEAT function repeats a string in the list of the data.
REPLACE	The REPLACE function replaces the circumstance of the substring in a string with a new substring.
REVERSE	The REVERSE function is used to reverses a string and returns the output.
RIGHT	The RIGHT function extracts several characters from the right of the string.
RTRIM	The RTRIM function uses to remove trailing spaces from a string.
SPACE	The SPACE function displays a string of the space characters
STRCMP	The STRCMP function compares two string values and returns the output.
SUBSTR	The SUBSTR function works to remove a substring from a starting string at any position.
TRIM	The TRIM function uses to remove leading and trail spaces from a string.

String Function Examples

MySQL string function works on the database information related string. You can find the length of the string, display string data in the required format, and operate string data as per applications. This function provides multiple operators to modify strings like concat, trim, reverse, and so on.

1. Example of the simple string function

This example shows basic operations about numbers and characters. Here, we have used the four-string functions and display the respective output.

mysql> select reverse("MySQL Tutorial Online") AS reverse,
      UCASE("MySQL Tutorial Online") AS upper_case,
      LCASE("MySQL Tutorial Online") AS lower_case,
      char_length("MySQL Tutorial Online") AS length;

Output

We can see four columns for four basic string functions with the "MySQL Tutorial Online" string value. The first column shows the reverse string and the second column displays the upper case string. The third column returns a lower case string, and the fourth column shows character length with space.

2. Example of the string function with multiple values

You can use several string functions in the MySQL query. The following function displays the left, right, and mid-value of the entire string data. Here, you can use the position function to get the place of the required character.

mysql> select left("MySQL Tutorial Online", 6) AS left_character,
       right("MySQL Tutorial Online", 6) AS right_character,
       mid("MySQL Tutorial Online", 7, 8) AS middle_character,
       position( "QL" IN "MySQL Tutorial Online") AS place_of_character;

Output

The above image shows the different characters of the string. The left function shows the first six characters, and the right function shows the last six characters of the string. The mid function shows the middle character of the string. The position function returns the available position of the first string in the second string.

3. Example of the string function with data

MySQL string function uses several functions to exchange, replace, and insert information. Here, you use the insert, replace, concat, and substring function with data. The "insert" function exchanges substring using position. MySQL substring function returns part of the string using position.

mysql> SELECT INSERT("MySQL Tutorial Online", 1, 5, "Database") AS insertdata,
     REPLACE("MySQL Tutorial Online", "mysql", "Java") AS exchange,
     CONCAT ("mysql", "online", "tutorial") AS combine,
     SUBSTRING("MySQL Tutorial Online", 7, 8) AS stringpart;

Output

Here, the MySQL output image returns four columns for four functions. The insert function exchanges substring from "MySQL" to "database" value. The replace function exchanges substring from "MySQL" to "java" value. The third function combines all string parts. The last function returns the required part of the string data.

4. Example of the string functions

This string function uses trim and RPAD and LPAD functions in the example. The trim function removes space on the left and right sides of the string. Here, the MySQL query uses RPAD and LPAD functions to add value to a string.

mysql> SELECT RTRIM("MySQL Tutorial Online               ") AS lefttrim,
    LTRIM("          MySQL Tutorial Online") AS righttrim,
    RPAD("MySQL Tutorial", 19, "learning") AS rightstring,
    LPAD("MySQL Tutorial", 19, "learning") AS leftstring;

Output

Here the trim function removes the right side of the space in the string. The ltrim function avoids left string space. The rpad and lpad functions insert specific data on the right and left sides, respectively.