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:

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. LOWER()

SELECT LOWER('Hello World') AS Lowercase_String;

Output:

+------------------+
| Lowercase_String |
+------------------+
| hello world |
+------------------+

14. LPAD()

SELECT LPAD('apple', 10, '*') AS Padded_String;

Output:

+---------------+
| Padded_String |
+---------------+
| *****apple |
+---------------+

15. LTRIM()

SELECT LTRIM(' Hello World ') AS Trimmed_String;

Output:

+----------------+
| Trimmed_String |
+----------------+
| Hello World |
+----------------+

16. MAKE_SET()

SELECT MAKE_SET(1, 'a', 'b', 'c') AS Set_Values;

Output:

+------------+
| Set_Values |
+------------+
| a |
+------------+

17. MID()

SELECT MID('Hello World', 7, 5) AS Extracted_String;

Output:

+------------------+
| Extracted_String |
+------------------+
| World |
+------------------+

18. OCTET_LENGTH()

SELECT OCTET_LENGTH('Hello World') AS Byte_Length;

Output:

+-------------+
| Byte_Length |
+-------------+
| 11 |
+-------------+

19. OCT()

SELECT OCT(42) AS Octal_Number;

Output:

+--------------+
| Octal_Number |
+--------------+
| 52 |
+--------------+

20. ORD()

SELECT ORD('A') AS Unicode_Code;

Output:

+--------------+
| Unicode_Code |
+--------------+
| 65 |
+--------------+

21. POSITION()

SELECT POSITION('bar' IN 'foobarbar') AS Position;

Output:

+----------+
| Position |
+----------+
| 4 |
+----------+

22. QUOTE()

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

Output:

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

23. REPLACE()

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

Output:

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

24. REPEAT()

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

Output:

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

25. RIGHT()

SELECT RIGHT('Hello World', 5) AS RightmostString;

Output:

+-----------------+
| RightmostString |
+-----------------+
| World |
+-----------------+

26. RPAD()

SELECT RPAD('apple', 10, '*') AS Padded_String;

Output:

+---------------+
| Padded_String |
+---------------+
| apple***** |
+---------------+

27. RTRIM()

SELECT RTRIM(' Hello World ') AS Trimmed_String;

Output:

+----------------+
| Trimmed_String |
+----------------+
| Hello World |
+----------------+

28. SOUNDEX()

SELECT SOUNDEX('Hello') AS Soundex_Value;

Output:

+---------------+
| Soundex_Value |
+---------------+
| H400 |
+---------------+

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