Understanding Byte vs. Character Length in Strings

• CHAR_LENGTH(string)
  Similar to LENGTH, it returns the number of characters in a string.
• LENGTH(string)
  This function returns the number of characters in a string.

These functions can be used within your SQL queries to determine the length of text data stored in your MariaDB tables.

For example, if you have a table named "Articles" with a column named "Content" that stores text content, you could use the following query to find articles with titles exceeding a certain character limit:

SELECT * FROM Articles
WHERE LENGTH(Content) > 100;

This query would retrieve all articles where the content in the "Content" column is longer than 100 characters.

---

Finding Article Titles with Specific Length

This query retrieves titles from the "Articles" table with a length between 10 and 20 characters (inclusive):

SELECT Title
FROM Articles
WHERE LENGTH(Title) BETWEEN 10 AND 20;

Checking Username Validity

This query ensures usernames in the "Users" table are within a specific character limit (8 to 16 characters):

SELECT Username
FROM Users
WHERE LENGTH(Username) BETWEEN 8 AND 16;

Differentiating Byte and Character Length (UTF-8)

This example demonstrates the difference between LENGTH and CHAR_LENGTH for UTF-8 encoded characters:

SET @text = 'こんにちは世界'; -- "Hello World" in Japanese (multi-byte characters)

SELECT @text AS text, LENGTH(@text) AS byte_length, CHAR_LENGTH(@text) AS char_length;

This will return:

text	byte_length	char_length
こんにちは世界	15	7

Checking for Empty Strings

This query uses LENGTH to identify empty strings in the "Comments" column of the "Posts" table:

SELECT *
FROM Posts
WHERE LENGTH(Comments) = 0;

---

• CHAR_LENGTH(string)
  与 LENGTH مشابه (yǔ LENGTH çimíle) - (Similar to LENGTH). Both functions return the number of characters in a string. However, CHAR_LENGTH might be useful for specific scenarios involving character encoding like UTF-8 (where a single character might be represented by multiple bytes).
• LENGTH(string)
  这 (zhè) 是 MariaDB 最常用的字符串长度函数 (zuì cháng yòng de zi符串长度函数) - (This is the most common string length function in MariaDB).

示例 (shíli)

-- 查找标题长度在 10 到 20 个字符之间的文章 (zhǎobài biao題长度 zài 10 dào 20 ge zìfù zhī jiān de wénzhāng)
SELECT Title FROM Articles WHERE LENGTH(Title) BETWEEN 10 AND 20;

额外的字符串处理功能 (é wài de zi符串 chǔlí gōngnéng)

MariaDB 提供其他字符串处理功能 (tígōng qíta zìfùchuán chǔlí gōngnéng) - (MariaDB offers other string manipulation functions):

• INSTR(string, substring)
  查找子字符串的位置 (zhǎobài zǐzìchuán de wèizhì) - (Finds the position of a substring).
• REPLACE(string, old_substring, new_substring)
  替换子字符串 (tíhuàn zǐzìchuán) - (Replaces a substring).
• SUBSTRING(string, start, length)
  提取子字符串 (tīqǐ zǐzìchuán) - (Extracts a substring).