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

Using LENGTH and CHAR_LENGTH to Measure Text in SQL

📖 Scenario: You work at a bookstore database. You want to find out how long the book titles are in terms of characters. This helps in formatting the display on the website.

🎯 Goal: Build SQL queries that use LENGTH and CHAR_LENGTH functions to measure the length of book titles.

📋 What You'll Learn

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

Insert three books with exact titles: 'SQL Basics', 'Learn SQL in 24 Hours', and 'Advanced SQL Techniques'.

Write a query to select each book's title and the length of the title using LENGTH(title).

Write a query to select each book's title and the character length of the title using CHAR_LENGTH(title).

💡 Why This Matters

🌍 Real World

Measuring text length helps format UI elements and validate input sizes in real applications like websites and apps.

💼 Career

Knowing LENGTH and CHAR_LENGTH is useful for database developers and analysts to handle text data correctly.

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 exact rows: (1, 'SQL Basics'), (2, 'Learn SQL in 24 Hours'), and (3, 'Advanced SQL Techniques').

SQL

-- Create the books table and insert the three books
-- Your code here

Hint

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

2

Add a query to measure length using LENGTH()

Write a SQL query that selects title and the length of the title using LENGTH(title) from the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn SQL in 24 Hours'),
(3, 'Advanced SQL Techniques');

-- Write a query selecting title and LENGTH(title)
-- Your code here

Hint

Use SELECT title, LENGTH(title) FROM books; to get the length of each title.

3

Add a query to measure length using CHAR_LENGTH()

Write a SQL query that selects title and the character length of the title using CHAR_LENGTH(title) from the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn SQL in 24 Hours'),
(3, 'Advanced SQL Techniques');

SELECT title, LENGTH(title) FROM books;

-- Write a query selecting title and CHAR_LENGTH(title)
-- Your code here

Hint

Use SELECT title, CHAR_LENGTH(title) FROM books; to get the character length of each title.

4

Complete the project with both queries

Ensure your SQL script includes the table creation, data insertion, and both queries: one using LENGTH(title) and one using CHAR_LENGTH(title).

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'SQL Basics'),
(2, 'Learn SQL in 24 Hours'),
(3, 'Advanced SQL Techniques');

SELECT title, LENGTH(title) FROM books;

SELECT title, CHAR_LENGTH(title) FROM books;

-- Finalize your SQL script here
-- Your code here

Hint

Make sure all parts are included in your final SQL script.

Practice

1. What is the main difference between LENGTH and CHAR_LENGTH functions in SQL?

easy

A. LENGTH counts characters, CHAR_LENGTH counts words.

B. LENGTH counts bytes, CHAR_LENGTH counts characters.

C. LENGTH counts words, CHAR_LENGTH counts bytes.

D. LENGTH and CHAR_LENGTH both count characters the same way.

Solution

1. Step 1: Understand what LENGTH counts

   LENGTH returns the number of bytes used by the string, which can be more than characters if multi-byte characters exist.

2. Step 2: Understand what CHAR_LENGTH counts

   CHAR_LENGTH returns the number of characters, counting multi-byte characters as one each.

3. Final Answer:

   LENGTH counts bytes, CHAR_LENGTH counts characters. -> Option B

4. Quick Check:

   LENGTH ≠ CHAR_LENGTH when multi-byte chars present [OK]

Hint: Remember: bytes ≠ characters for multi-byte text [OK]

Common Mistakes:

• Confusing bytes with characters
• Assuming both functions return the same count
• Thinking CHAR_LENGTH counts words

2. Which of the following is the correct syntax to get the number of characters in the column name using SQL?

easy

A. SELECT CHAR_LENGTH(name) FROM users;

B. SELECT COUNT(name) FROM users;

C. SELECT LENGTH(name) FROM users;

D. SELECT SIZE(name) FROM users;

Solution

1. Step 1: Identify function for character count

   CHAR_LENGTH is the correct function to count characters in a string column.

2. Step 2: Check syntax correctness

   SELECT CHAR_LENGTH(name) FROM users; is valid SQL syntax to get character count per row.

3. Final Answer:

   SELECT CHAR_LENGTH(name) FROM users; -> Option A

4. Quick Check:

   Use CHAR_LENGTH() for character count [OK]

Hint: Use CHAR_LENGTH() to count characters, not LENGTH() [OK]

Common Mistakes:

• Using LENGTH() when characters count is needed
• Using COUNT() which counts rows, not string length
• Using non-existent SIZE() function

3. Given a table messages with a column text containing the value 'café', what will SELECT LENGTH(text), CHAR_LENGTH(text) FROM messages; return?

medium

A. (5, 5)

B. (4, 4)

C. (4, 5)

D. (5, 4)

Solution

1. Step 1: Understand byte vs character count for 'café'

   The letter 'é' is a 2-byte character in UTF-8, so LENGTH counts bytes: 'c'(1) + 'a'(1) + 'f'(1) + 'é'(2) = 5 bytes.

2. Step 2: Count characters with CHAR_LENGTH

   CHAR_LENGTH counts characters, so 'c', 'a', 'f', 'é' = 4 characters.

3. Final Answer:

   (5, 4) -> Option D

4. Quick Check:

   LENGTH = bytes, CHAR_LENGTH = characters [OK]

Hint: Multi-byte chars increase LENGTH but not CHAR_LENGTH [OK]

Common Mistakes:

• Assuming LENGTH and CHAR_LENGTH return same value
• Counting bytes as characters
• Ignoring multi-byte encoding

4. You wrote this query: SELECT CHAR_LENGTH(name) FROM users WHERE LENGTH(name) > 5; but it returns rows even though some names look shorter than 5 characters. What is the likely issue?

medium

A. The LENGTH function counts bytes, so multi-byte characters make the length larger than character count.

B. The CHAR_LENGTH function is not supported in SQL.

C. The query syntax is incorrect; LENGTH cannot be used in WHERE clause.

D. The name column contains NULL values causing the filter to fail.

Solution

1. Step 1: Understand LENGTH in WHERE clause

   LENGTH(name) counts bytes, so names with multi-byte characters may have byte length greater than character count.

2. Step 2: Reason why rows are returned unexpectedly

   If names have fewer than or equal to 5 characters but more than 5 bytes, the condition LENGTH(name) > 5 succeeds, so rows match unexpectedly.

3. Final Answer:

   The LENGTH function counts bytes, so multi-byte characters make the length larger than character count. -> Option A

4. Quick Check:

   LENGTH counts bytes, affects WHERE filters [OK]

Hint: Remember LENGTH counts bytes, affects filtering with multi-byte text [OK]

Common Mistakes:

• Thinking LENGTH counts characters
• Assuming CHAR_LENGTH unsupported
• Believing LENGTH can't be in WHERE clause

5. You want to find all rows in table comments where the text length is exactly 10 characters, including multi-byte characters. Which query is correct?

hard

A. SELECT * FROM comments WHERE LENGTH(text) = CHAR_LENGTH(text);

B. SELECT * FROM comments WHERE LENGTH(text) = 10;

C. SELECT * FROM comments WHERE CHAR_LENGTH(text) = 10;

D. SELECT * FROM comments WHERE LENGTH(text) > 10 AND CHAR_LENGTH(text) < 10;

Solution

1. Step 1: Identify correct function for character count

   To count characters including multi-byte ones, use CHAR_LENGTH.

2. Step 2: Write condition for exact character length

   Use WHERE CHAR_LENGTH(text) = 10 to filter rows with exactly 10 characters.

3. Final Answer:

   SELECT * FROM comments WHERE CHAR_LENGTH(text) = 10; -> Option C

4. Quick Check:

   Use CHAR_LENGTH for character count filters [OK]

Hint: Use CHAR_LENGTH() to filter by character count, not LENGTH() [OK]

Common Mistakes:

• Using LENGTH() which counts bytes, not characters
• Confusing LENGTH and CHAR_LENGTH equality
• Using contradictory conditions in WHERE clause