String Functions in MySQL
Overview
MySQL string function is used to perform some specific operation related to string in MySQL. String functions in MySQL can be used to perform various manipulations and operations on the strings in a database.
Using the MySQL string functions, we can perform various operations such as concatenation, substring extraction, character case alteration, whitespace trimming, character replacement, etc. Many types of functions in MySQL are used to manipulate the data from the database such as Numeric functions, String functions, aggregate functions, etc.
Introduction
As we know, MySQL is a database management system that allows the user to store and retrieve data from large datasets. But MySQL is not only limited to storing and accessing data. Various operations can be performed over these data using the functions in MySQL. Using functions to modify and manipulate the data increases the time efficiency and it also it makes easy for the user to fetch data using the MySQL function. These functions are a piece of code that is specifically designed to perform a specific task and it returns an output. To execute every different task, there is a separate function.
There are various types of functions in MySQL such as String functions, Numeric functions, Date functions, and Aggregate functions. Here we will discuss the String functions in MySQL. Mysql string functions are used to perform operations over the string characters present in a MySQL table. The various examples of string functions in MySQL are the RIGHT function which is used to access the rightmost character of a particular string. The CONCAT function is used to add two strings together and get a single output as the result. Like there, there are numerous string functions that we will discuss in this article.
But before going to string functions, let us see why we need a function in MySQL. Why can not we simply write the codes and fetch the data in the below section?
Why Functions in MySQL?
The format of the data will not same in every system. Using functions to manipulate data helps to gain consistency and reduce re-work time and data inconsistency over the data. One more reason to use a function in MySQL is that functions reduce the network traffic over the server application or client application. All these measures increase the performance of the system and increase the productivity of a user.
Prerequisites
Some pre-requisites need to be available in your system for doing operations using the string functions in MySQL which are as follows:
- MySQL Server should be installed
- MySQL Shell should be installed
- MySQL user account with access to the root
Syntax
The syntax of the MySQL string function is as follows:
The syntax of the MySQL string function is defined as the name of the string function with parentheses. Inside the parentheses, we defined the arguments required for the string function if needs any. Otherwise, we simply use the function name with parentheses.
For example:
MySQL String Functions Cheat Sheet
| String function | Description |
|---|---|
| ASCII() | This string function is used to return a numeric value that represents the ASCII value. It represents the leftmost character of a particular string. If no string is specified, this function returns null and it also returns NULL, in case a string is null. |
| BIN() | This function is used to convert an integer value into its binary equivalent as a string. |
| BIT_LENGTH() | This string function is used to get the length of a particular string in the form of bits. Example: SELECT BIT_LENGTH('Hello World'); -- Output: 88 |
| CHAR() | This string function accepts a numeric value as its argument and returns a string of characters by the ASCII table. This function skips any kind of NULL values. Example: SELECT CHAR(65); -- Output: A |
| CHARACTER_LENGTH() | This string function provides the length of a particular string which is measured in characters. It multiplies the characters of the string as a single character. |
| CONCAT() | This string function is used to add two or more strings end-to-end. This function first converts all the provided arguments to two string types and then performs concatenation. Example: CONCAT('Hello' , 'Everyone') --Output: HelloEveryone |
| CONCAT_WS() | This string function takes two or more two expressions together included by a separator. That particular separator is used to split the different strings in which the user wants to perform the concatenation. The result will be NULL if the separator is NULL. |
| ELT() | This string function is used to specify which string from N arguments in the function needs to be returned as a result. This function returns NULL, if the value of N is less than 1 or exceeds the number of provided strings. |
| EXPORT_SET() | This string function is used to return either of two strings, ON or OFF. This string represents every single bit of the first argument from right to left. Even if the argument is an integer, this function converts the integer into bits. |
| FIELD() | This string function is used to get the index position of a particular string from the string list. If there is no specified string present in the string list, the output will be zero. The FIELD is a case-sensitive function. Example: FIELD("B", "A", "B", "C", "D", "E", "F") --Output: 2 |
| FIND_IN_SET() | This string function returns an integer value that represents the position of a particular string in the string list. If there is more than one similar string instance, the function only returns the first position of the particular string |
| FORMAT() | This string function is used to provide an output representing the number of X which is rounded to a particular number of decimal places. The locale D can be also specified by the user, which will affect the resultant output. |
| FROM_BASE64() | This string function is used to represent a binary number which represents the decoded format of a base-64 string value. It returns a NULL in case of a NULL argument. The reverse of this function is TO_BASE64(). Example: FROM_BASE64('aGVsbG8=') --Output: hello |
| HEX() | This string function returns a string value that represents a hexadecimal value for a specified N decimal number. If there is a string argument, the function converts each of these characters into two hexadecimal digits. |
| INSERT() | The INSERT() function in MySQL is used to insert a string into another string at a specified position and for a specified length. This function returns NULL if any of the arguments in this function is NULL. Example: INSERT('Whatever' , 5 , 0, 'so') --Output: Whatsoever |
| INSTR() | This string function is used to provide the output of the first appearance of the given substring in that particular original string. This function is similar to the LOCATE() function, the only difference is the order of argument is reversed. |
| LEFT() | This string function provides an output on the number of characters present on the leftmost side of the particular string provided in the argument. The output is NULL for any NULL argument. |
| LENGTH() | This string function is used to provide the length of a given string in bytes. If there are multi-byte characters, they will count as multiple bytes. |
| LOAD_FILE() | This string function is used to read a file and provide output a string value that contains that particular byte. Full file path and file privilege are the two must two prerequisites to execute this function. |
| LOCATE(), i.e., POSITION() | This string function is used to provide an output that represents the position of the first occurrence for that particular argument provided in the string. In this function, the position argument is used to direct the function from where to start the search. Neglecting this argument will automatically start searching from the beginning. |
| LOWER() | This string function is used to convert all the characters in the provided string to lowercase and print as the output. The default character set mapping used by this function is utf8mb4. The LOWER() function is the same as the LCASE() function. Example: TOLOWER('HELLO') --Output: hello |
| LPAD() | This string function is used to specify the string left padded with the argument 'padstr' string up to some specified length of character. If the string argument is bigger than the length argument, then this function automatically shortens the output by the length of characters. |
| LTRIM() | This string function is used to provide an output that represents the string value without leading the space characters in the arguments of the function. |
| MAKE_SET() | This string function is used to provide an output as a set value which is a string type and it contains the corresponding bit specified for those particular substrings of the argument. If any of the arguments is NULL, the resultant output will not be displayed. |
| MATCH() | This string function is used to perform searching of text values specified in a list of texts which is separated by commas. You can add a "search_modifier" argument to perform some specific type of search. |
| NOT REGEXP | This string function is used to find some matching patterns between the two arguments provided in this function. The opposite of this function is "REGEXP". |
| OCT() | This string function is used to get the octal value of the given arguments in this function. It results in NULL if no arguments are passed in this function. Example: OCT('50') --Output: 62 |
| ORD() | This string function is used to get the code of the leftmost character of the given string, only if the character is multibyte. If the character is leftmost character is not multibyte, the function returns the ASCII value of the character. |
| QUOTE() | This string function provides an output that represents escaped data that are usable in the MySQL statement. The single quotes are used to enclose the given strings and it also contains backslash. |
| REGEXP_LIKE(), REGEXP, RLIKE | This string function is used to perform a matching operation between the arguments specified in the function. If there is a match between the arguments, the output will be 1. Otherwise, the resultant output will be zero. you can provide, the "match_type" argument. If you want to specify any matching type. |
| REGEXP_INSTR() | This string function is used to provide the location of an expression pattern in the given string. You can find a substring in a string by using this function. If there is no matching between them, the function returns 0. |
| REGEXP_REPLACE() | This string function is used to replace the substrings from the provided strings in the function. If there is a match found, you will get the whole string as a replaced output. |
| REGEXP_SUBSTR() | This string function is used to search a string for a given expression in the function. This function is similar to REGEXP_INSTR, but this function returns the substring instead of returning the position of that particular substring. |
| REPEAT() | This string function is used to provide a string that can be repeated many times as per the user's choice. If the repeat argument is less than 1, the output will be an empty string. |
| REPLACE() | This string function is used to replace the occurrences of a particular string provided in the function. |
| REVERSE() | This string function is used to reverse the characters of the string provided in the function. Example: REVERSE('hello') --Output: olleh |
| RIGHT() | This string function is used to return the rightmost number of characters from that particular string. |
| RPAD() | This string function is used to append the string the specified number of times by the user. |
| RTRIM() | This string function is used to remove any trailing spaces from the provided strings. |
| SOUNDEX() | This string function is used to return a special type of soundex string. When two strings are similar in sound, they also have the same soundex strings. |
| SPACE() | This string function is used to return a string that contains n number of space characters. |
| STRCMP() | This string function is used to perform a comparison between two strings. If the strings are equal, it returns 0 and if the first argument is smaller than the second argument, it returns -1. And it returns 1 if the first argument is greater than the second argument. |
| SUBSTRING() | This string function is used to return the substrings present in the strings that are being used as the argument in the function. |
| SUBSTRING_INDEX() | This string function is used to return a substring taken from that string before counting occurrences of the argument denim. Example: SUBSTRING_INDEX('www.example_website.com', '.', 2) --Output: www.example_website |
| TO_BASE64() | This string function is used to convert the provided string into the form base-64 and return them as the output. |
| TRIM() | This string function is used to remove the leading spaces and trailing spaces specified by the user. |
| UPPER() | This string function is used to convert all the string characters into uppercase. It is the same as the UCASE() function. |
| UNHEX() | This string function is used to convert the pair of hexadecimal digits from an argument to a character value. |
| WEIGHT_STRING() | This string function is used to return the weight string provided for the input as an argument. |
Conclusion
- There are many types of functions present in MySQL that are used to manipulate the data from the database such as Numeric functions, String functions, aggregate functions, etc.
- MySQL string functions are used to perform operations over the string characters present in a MySQL table.
- Using functions to manipulate data helps to gain consistency and reduce re-work time and data inconsistency over the data, and it also reduces the traffic over the network for both client and server applications.
- Pre-requisites for doing operations using the string functions in MySQL are MySQL server, MySQL Shell, and access to root.
- The syntax of the MySQL string function is string_function_name(argument_1, argument_2, ...).
- ASCII() string function is used to return a numeric value that represents the ASCII value.
- FROM_BASE64() function is used to represent a binary number which represents the decoded format of a base-64 string value. It returns a NULL in case of a NULL argument and the reverse of this function is TO_BASE64().
- LOWER() function is used to convert all the characters in the provided string to lowercase and print as the output.
- TRIM() function is used to remove the leading spaces and trailing spaces specified by the user.