MySQL – String Functions

MySQL is one of the most widespread relational database management systems that covers many functions to manipulate the strings precisely. Processing text data in MySQL can go from a simple concatenation to pattern matching/substring extraction. In this article, we will be considering some commonly used MySQL string functions and their syntax, usage as well as examples.

MySQL – String Functions

Some most important String Functions are defined below:

Function	Description
CONCAT_WS()	It Concatenates strings with a specified separator.
CONCAT()	It Concatenates two or more strings.
CHARACTER_LENGTH()	It Returns the number of characters in a string.
ELT()	It Returns the string at the specified index from a list of strings.
EXPORT_SET()	It Returns a string where each bit of a bitmap value corresponds to a value in a set.
FIELD()	It Returns the index (position) of a string in a list of strings.
FIND_IN_SET()	It Returns the position of a string within a comma-separated list of strings.
FORMAT()	It Formats a number to a format like ‘#,###,###.##’, rounded to a specified number of decimal places.
FROM_BASE64()	It Decodes a base64-encoded string.
HEX()	It Returns a string representation of a hexadecimal value.
INSERT()	It Inserts a substring into a string at a specified position and for a certain number of characters
INSTR()	It Returns the position of the first occurrence of a substring in a string.
LENGTH()	The LENGTH() function returns the length of a string in bytes.
REPLACE()	The REPLACE() function replaces all occurrences of a substring within a string with another substring
QUOTE()	It Returns a string enclosed in single quotes, with special characters escaped.
REVERSE()	It Reverses a string.
REPEAT()	It Repeats a string a specified number of times.
RIGHT()	It Returns the rightmost characters of a string.

Some Examples of MySQL – String Functions

1. CONCAT_WS()

SELECT CONCAT_WS(', ', 'apple', 'banana', 'orange') AS Concatenated_String;

Output:

+----------------------+
| Concatenated_String |
+----------------------+
| apple, banana, orange |
+----------------------+

2. CONCAT()

SELECT CONCAT('Hello', ' ', 'World') AS Concatenated_String;

Output:

+-------------------+
| Concatenated_String |
+-------------------+
| Hello World       |
+-------------------+

3. CHARACTER_LENGTH()

SELECT CHARACTER_LENGTH('Hello World') AS String_Length;

Output:

+--------------+
| String_Length |
+--------------+
| 11           |
+--------------+

4. ELT()

SELECT ELT(3, 'apple', 'banana', 'orange', 'grape') AS Selected_String;

Output:

+----------------+
| Selected_String |
+----------------+
| orange         |
+----------------+

5. EXPORT_SET()

SELECT EXPORT_SET(5, 2, '0', ',', '1') AS Binary_Set;

Output:

+------------+
| Binary_Set |
+------------+
| 1,0,1      |
+------------+

6. FIELD()

SELECT FIELD('banana', 'apple', 'banana', 'orange') AS Position;

Output:

+----------+
| Position |
+----------+
| 2        |
+----------+

7. FIND_IN_SET()

SELECT FIND_IN_SET('banana', 'apple,banana,orange') AS Position;

Output:

+----------+
| Position |
+----------+
| 2        |
+----------+

8. FORMAT()

SELECT FORMAT(1234567.89, 2) AS Formatted_Number;

Output:

+-------------------+
| Formatted_Number |
+-------------------+
| 1,234,567.89     |
+-------------------+

9. FROM_BASE64()

SELECT FROM_BASE64('SGVsbG8gV29ybGQ=') AS Decoded_String;

Output:

+-------------------+
| Decoded_String    |
+-------------------+
| Hello World       |
+-------------------+

10. HEX()

SELECT HEX('Hello') AS Hexadecimal_Value;

Output:

+-------------------+
| Hexadecimal_Value |
+-------------------+
| 48656C6C6F        |
+-------------------+

11. INSERT()

SELECT INSERT('Hello World', 7, 0, 'Beautiful ') AS Modified_String;

Output:

+---------------------+
| Modified_String     |
+---------------------+
| Hello Beautiful World |
+---------------------+

12. INSTR()

SELECT INSTR('Hello World', 'Wor') AS Position;

Output:

+----------+
| Position |
+----------+
| 7        |
+----------+

13. REPLACE()

SELECT REPLACE('Hello World', 'World', 'Universe') AS Modified_String;

Output:

+------------------+
| Modified_String  |
+------------------+
| Hello Universe   |
+------------------+

14. QUOTE()

SELECT QUOTE('It\'s a beautiful day!') AS Quoted_String;

Output:

+------------------------+
| Quoted_String          |
+------------------------+
| 'It\'s a beautiful day!'|
+------------------------+

15. REPEAT()

SELECT REPEAT('Hello ', 3) AS Repeated_String;

Output:

+------------------+
| Repeated_String  |
+------------------+
| Hello Hello Hello |
+------------------+

Conclusion

MySQL string functions have been developed to enable users do a lot of textual data manipulation in their databases. Whether you want to concatenate strings, extract substrings or execute complex pattern matching, MySQL has tools that are both efficient and powerful enough to fit your tasks. Good in database operations and data manipulation management, you will be able to improve your database processes and save your efforts