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
Similar Reads
SQL | String functions SQL String Functions are powerful tools that allow us to manipulate, format, and extract specific parts of text data in our database. These functions are essential for tasks like cleaning up data, comparing strings, and combining text fields. Whether we're working with names, addresses, or any form
7 min read
PostgreSQL String Functions PostgreSQL is a powerful, open-source relational database management system that offers a rich set of functions and operators for working with string data. String manipulation is an essential task in many applications, and PostgreSQL provides a variety of built-in functions to make working with text
8 min read
PostgreSQL - Substring Function PostgreSQL is a powerful relational database management system with extensive text processing functions, including the flexible SUBSTRING function. This function enables users to extract specific portions of a string, making it essential for text manipulation, especially when dealing with large data
4 min read
PostgreSQL - TRIM Function The TRIM() function in PostgreSQL is an essential tool for removing unwanted characters from strings. Whether we're working with user inputs, formatting text, or performing data cleansing operations, TRIM() is an invaluable function for managing string data. This article will provide an in-depth loo
4 min read
REPLACE() Function in MySQL The REPLACE() function in MySQL is a powerful tool for string manipulation, allowing users to substitute specific substrings within a larger string. This functionality is particularly useful in various applications such as updating text data, cleaning up input or adjusting content in a database. In
3 min read