MySQL - String Functions

Last Updated : 27 Jun, 2024

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.
LIKE()	The `LIKE()` function is used for pattern matching in SQL queries. It allows you to search for a specified pattern in a string.
LOAD_FILE()	The `LOAD_FILE()` function reads the content of a file on the server and returns it as a string.
LOCATE()	The `LOCATE()` function returns the position of the first occurrence of a substring within a string.
LOWER()	The `LOWER()` function converts all characters in a string to lowercase.
LPAD()	The `LPAD()` function pads a string to the left with another string to a certain length.
LTRIM()	The `LTRIM()` function removes leading spaces (or any specified characters) from a string.
MAKE_SET()	The `MAKE_SET()` function returns a set of bits that corresponds to the values of the arguments.
MID()	The `MID()` function extracts a substring from a string, starting from a specified position.
OCTET_LENGTH()	The `OCTET_LENGTH()` function returns the length of a string in bytes.
OCT()	The `OCT()` function converts a number from decimal to octal format.
ORD()	The `ORD()` function returns the Unicode code point value of the leftmost character of a string.
POSITION()	The `POSITION()` function returns the position of the first occurrence of a substring within a string.
QUOTE()	It Returns a string enclosed in single quotes, with special characters escaped.
REPLACE()	The REPLACE() function replaces all occurrences of a substring within a string with another substring
RPAD()	The `RPAD()` function pads a string to the right with another string to a certain length.
REVERSE()	It Reverses a string.
REPEAT()	It Repeats a string a specified number of times.
RIGHT()	It Returns the rightmost characters of a string.
SOUNDEX()	The `SOUNDEX()` function returns a phonetic representation of a string.

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

MySQL - String Functions

muditgu1tud

Improve

Article Tags :

MySQL - String Functions