MySQL String/Text Functions

MySQL offers a variety of string functions that allow developers and database administrators to manipulate and handle text data efficiently. These functions are useful for searching, formatting, and manipulating strings stored in databases. Below is a comprehensive guide to MySQL string functions, complete with syntax and examples to help you understand their usage.

1. CONCAT()

Purpose: Concatenates two or more strings.

Syntax:

CONCAT(string1, string2, ..., stringN);

Example:

SELECT CONCAT('Hello', ' ', 'World!') AS Result;
-- Output: 'Hello World!'

---

2. LENGTH()

Purpose: Returns the length of a string in bytes.

Syntax:

LENGTH(string);

Example:

SELECT LENGTH('Hello') AS Length;
-- Output: 5

---

3. CHAR_LENGTH() / CHARACTER_LENGTH()

Purpose: Returns the length of a string in characters.

Syntax:

CHAR_LENGTH(string);
CHARACTER_LENGTH(string);

Example:

SELECT CHAR_LENGTH('Hello') AS CharLength;
-- Output: 5

---

4. LOWER()

Purpose: Converts all characters in a string to lowercase.

Syntax:

LOWER(string);

Example:

SELECT LOWER('MYSQL') AS Lowercase;
-- Output: 'mysql'

---

5. UPPER()

Purpose: Converts all characters in a string to uppercase.

Syntax:

UPPER(string);

Example:

SELECT UPPER('mysql') AS Uppercase;
-- Output: 'MYSQL'

---

6. SUBSTRING() / SUBSTR()

Purpose: Extracts a substring from a string.

Syntax:

SUBSTRING(string, start, length);

Example:

SELECT SUBSTRING('Hello World', 7, 5) AS SubStr;
-- Output: 'World'

---

7. TRIM()

Purpose: Removes leading and trailing spaces from a string.

Syntax:

TRIM(string);

Example:

SELECT TRIM('   Hello World   ') AS Trimmed;
-- Output: 'Hello World'

---

8. LTRIM()

Purpose: Removes leading spaces from a string.

Syntax:

LTRIM(string);

Example:

SELECT LTRIM('   Hello World') AS LTrimmed;
-- Output: 'Hello World'

---

9. RTRIM()

Purpose: Removes trailing spaces from a string.

Syntax:

RTRIM(string);

Example:

SELECT RTRIM('Hello World   ') AS RTrimmed;
-- Output: 'Hello World'

---

10. REPLACE()

Purpose: Replaces occurrences of a substring within a string with another substring.

Syntax:

REPLACE(original_string, substring_to_replace, replacement_string);

Example:

SELECT REPLACE('Hello World', 'World', 'MySQL') AS Replaced;
-- Output: 'Hello MySQL'

---

11. INSTR()

Purpose: Returns the position of the first occurrence of a substring in a string.

Syntax:

INSTR(string, substring);

Example:

SELECT INSTR('Hello World', 'World') AS Position;
-- Output: 7

---

12. REPEAT()

Purpose: Repeats a string a specified number of times.

Syntax:

REPEAT(string, count);

Example:

SELECT REPEAT('MySQL', 3) AS Repeated;
-- Output: 'MySQLMySQLMySQL'

---

13. REVERSE()

Purpose: Reverses the order of characters in a string.

Syntax:

REVERSE(string);

Example:

SELECT REVERSE('MySQL') AS Reversed;
-- Output: 'LQSyM'

---

14. LPAD()

Purpose: Pads the left side of a string with another string.

Syntax:

LPAD(string, length, pad_string);

Example:

SELECT LPAD('MySQL', 8, '-') AS LeftPadded;
-- Output: '---MySQL'

---

15. RPAD()

Purpose: Pads the right side of a string with another string.

Syntax:

RPAD(string, length, pad_string);

Example:

SELECT RPAD('MySQL', 8, '-') AS RightPadded;
-- Output: 'MySQL---'

---

16. SPACE()

Purpose: Returns a string of spaces.

Syntax:

SPACE(number);

Example:

SELECT SPACE(5) AS FiveSpaces;
-- Output: '     '

---

17. FIND_IN_SET()

Purpose: Returns the position of a string in a comma-separated list.

Syntax:

FIND_IN_SET(string, string_list);

Example:

SELECT FIND_IN_SET('apple', 'banana,apple,orange') AS Position;
-- Output: 2

---

18. FORMAT()

Purpose: Formats a number as a string with grouped thousands.

Syntax:

FORMAT(number, decimal_places);

Example:

SELECT FORMAT(1234567.891, 2) AS FormattedNumber;
-- Output: '1,234,567.89'

---

19. LEFT()

Purpose: Returns the left part of a string with the specified number of characters.

Syntax:

LEFT(string, length);

Example:

SELECT LEFT('MySQL Database', 5) AS LeftPart;
-- Output: 'MySQL'

---

20. RIGHT()

Purpose: Returns the right part of a string with the specified number of characters.

Syntax:

RIGHT(string, length);

Example:

SELECT RIGHT('MySQL Database', 8) AS RightPart;
-- Output: 'Database'

---

Conclusion

These string functions in MySQL make it easy to work with text data in your databases. By understanding and utilizing these functions, you can perform a variety of tasks such as searching, formatting, and modifying strings efficiently.

Stay tuned for more tutorials and database tips from ITXperts!

---