LENGTH and CHAR_LENGTH in MySQL - Mini Project: Build & Apply

Using LENGTH and CHAR_LENGTH in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to analyze the titles of books to understand their length in bytes and characters.

🎯 Goal: Build a simple MySQL query that shows the length in bytes and the number of characters for each book title in the books table.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert exactly three books with these titles: 'SQL Basics', 'Learn MySQL', 'データベース入門' (Japanese for 'Database Introduction').

Write a SELECT query that returns the title, the byte length using LENGTH(title), and the character length using CHAR_LENGTH(title).

Order the results by id ascending.

💡 Why This Matters

🌍 Real World

Understanding text length in bytes and characters helps when storing and indexing text data, especially with multi-byte characters like Japanese.

💼 Career

Database developers and administrators often need to analyze and manipulate string data efficiently for storage and search optimization.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Then insert these three rows exactly: (1, 'SQL Basics'), (2, 'Learn MySQL'), and (3, 'データベース入門').

MySQL

-- Your code here to create the table and insert the three rows

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the rows.

2

Add a SELECT query to get titles

Write a SELECT query to get the title column from the books table.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn MySQL'),
(3, 'データベース入門');

-- Write SELECT query to get title
SELECT title FROM books;

Need a hint?

Use SELECT title FROM books; to get the titles.

3

Add LENGTH and CHAR_LENGTH columns

Modify the SELECT query to also show LENGTH(title) as byte_length and CHAR_LENGTH(title) as char_length.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn MySQL'),
(3, 'データベース入門');

-- Select title, LENGTH(title) as byte_length, CHAR_LENGTH(title) as char_length
SELECT title, LENGTH(title) AS byte_length, CHAR_LENGTH(title) AS char_length FROM books;

Need a hint?

Use LENGTH(title) AS byte_length and CHAR_LENGTH(title) AS char_length in your SELECT.

4

Order results by id

Add an ORDER BY id ASC clause to the SELECT query to sort the results by id in ascending order.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn MySQL'),
(3, 'データベース入門');

-- Add ORDER BY id ASC to the SELECT query
SELECT title, LENGTH(title) AS byte_length, CHAR_LENGTH(title) AS char_length FROM books ORDER BY id ASC;

Need a hint?

Use ORDER BY id ASC at the end of your SELECT query.