mysql string conversion

MySQL is an open source and widely used relational database management system. In MySQL, there are many built-in string functions that make string manipulation and conversion easy.

This article will introduce the commonly used string conversion functions in MySQL, so that readers can operate string type data more skillfully.

1. Classification of string conversion functions

In MySQL, string conversion functions can be mainly divided into the following categories:

1. String case conversion function

• UPPER(): Convert the string to uppercase letters.
• LOWER(): Convert the string to lowercase letters.
• INITCAP(): Convert the first letter of the string to uppercase letters and other letters to lowercase letters.

2. String encoding conversion function

• CONVERT(): Convert a string from one character set to another. Commonly used character set names include "utf8", "gbk", "big5", etc.

3. String type conversion function

• CAST(): Convert one data type to another data type.
• CONVERT(): It can perform both string encoding conversion and string type conversion.

4. String replacement function

• REPLACE(): Replace a substring that appears in a string with another string.

5. String trimming function

• TRIM(): Remove leading and trailing spaces in the string.
• LTRIM(): Remove spaces in front of the string.
• RTRIM(): Remove spaces after the string.

6. String interception function

• SUBSTR() / SUBSTRING(): Intercept a part of a string and return the substring.
• LEFT(): Intercept a part of a string starting from the left and return this substring.
• RIGHT(): Intercept a part of a string starting from the right and return this substring.

7. Other string functions

• CONCAT(): Splice multiple strings together.
• LENGTH(): Returns the length of a string.
• LOCATE(): Returns the position where a certain character or substring appears for the first time in the string.
• REPEAT(): Repeat a string multiple times.

2. Examples

Let’s look at some examples of commonly used string conversion functions:

1. String case conversion

SELECT UPPER('hello, world!'); -- Output HELLO, WORLD!
SELECT LOWER('HELLO, WORLD!'); -- Output hello, world!
SELECT INITCAP ('hello, world!'); -- Output Hello, world!

2. String encoding conversion

SELECT CONVERT('Hello', 'gbk', 'utf8'); -- Output the GBK encoding format of "Hello"

3. String type conversion

SELECT CAST('123' AS SIGNED); -- Output 123
SELECT CONVERT('123', UNSIGNED); -- Output 123

4. String replacement

SELECT REPLACE('hello, world!', 'world', 'MySQL'); -- Output "hello, MySQL!"

5. String trimming

SELECT TRIM(' hello, world! '); -- Output "hello, world!"
SELECT LTRIM(' hello, world! '); -- Output "hello, world! "
SELECT RTRIM(' hello, world! '); -- Output " hello, world!"

6. String interception

SELECT SUBSTRING('hello, world!', 7); -- Output "world!"
SELECT LEFT('hello, world!', 5); -- Output "hello,"
SELECT RIGHT('hello, world!', 6); -- Output "world!"

7. Other string functions

SELECT CONCAT('hello', ', ', 'world!'); -- Output "hello, world!"
SELECT LENGTH('hello, world !'); -- Output 13
SELECT LOCATE('world', 'hello, world!'); -- Output 7
SELECT REPEAT('hello', 3); -- Output "hellohellohello"

3. Summary

This article provides a detailed introduction to some commonly used string conversion functions in MySQL, including case conversion, encoding conversion, type conversion, string replacement, string trimming, String interception, etc.

Proficient use of these functions can greatly improve the efficiency and accuracy of processing string data in MySQL.

It is worth noting that when using string functions, the length and encoding format of the string should be considered to avoid problems such as garbled characters.

I hope readers can gain a deeper understanding and mastery of the relevant knowledge of string conversion in MySQL through the introduction of this article.

The above is the detailed content of mysql string conversion. For more information, please follow other related articles on the PHP Chinese website!