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.