Bird
Raised Fist0
SQLquery~5 mins

Why string functions matter in queries in SQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
String functions help us change and check text in databases easily. They make searching and organizing text data simple and fast.
When you want to find all names starting with a certain letter.
When you need to clean up extra spaces from user input.
When you want to combine first and last names into one full name.
When you want to change text to all uppercase or lowercase for comparison.
When you want to extract a part of a text, like area code from a phone number.
Syntax
SQL
FUNCTION_NAME(column_name)
-- Example: UPPER(name)
-- Example: LENGTH(description)
String functions usually take a text column or value as input.
They return a new text or number based on the operation.
Examples
Changes all letters in the 'name' column to uppercase.
SQL
SELECT UPPER(name) FROM users;
Returns the number of characters in the 'description' column.
SQL
SELECT LENGTH(description) FROM products;
Joins first and last names with a space in between.
SQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
Removes extra spaces from the start and end of the 'email' column.
SQL
SELECT TRIM(email) FROM contacts;
Sample Program
This query creates a table and adds some customers. Then it selects their id, full name in uppercase, and email without extra spaces.
SQL
CREATE TABLE customers (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers VALUES
(1, 'Alice', 'Smith', ' alice@example.com '),
(2, 'Bob', 'Brown', 'bob@example.com'),
(3, 'Carol', 'Davis', ' carol@example.com');

SELECT id, CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name, TRIM(email) AS clean_email FROM customers;
OutputSuccess
Important Notes
String functions can help fix messy data before using it.
Using string functions in queries can speed up searching and sorting.
Remember that some string functions may behave differently depending on the database system.
Summary
String functions let you change and check text data easily in queries.
They help clean, combine, and compare text for better results.
Using them makes working with text in databases faster and simpler.

Practice

(1/5)
1. Why do we use string functions like UPPER() or LOWER() in SQL queries?
easy
A. To perform mathematical calculations on numbers
B. To delete rows from a table
C. To create new tables in the database
D. To change text case for consistent comparison or display

Solution

  1. Step 1: Understand the purpose of string functions

    Functions like UPPER() and LOWER() change the case of text data to uppercase or lowercase.

  2. Step 2: Recognize their use in queries

    Changing case helps compare text consistently or display it uniformly, which is important for searching or reporting.

  3. Final Answer:

    To change text case for consistent comparison or display -> Option D

  4. Quick Check:

    String functions change text case = B [OK]
Hint: String functions change or check text, not numbers [OK]
Common Mistakes:
  • Confusing string functions with numeric functions
  • Thinking string functions create or delete tables
  • Assuming string functions modify database structure
2. Which of the following is the correct syntax to get the first 3 characters of a column named name in SQL?
easy
A. SELECT MID(name, 3) FROM table;
B. SELECT LEFT(name, 3) FROM table;
C. SELECT RIGHT(name, 3) FROM table;
D. SELECT SUBSTRING(name FROM 3) FROM table;

Solution

  1. Step 1: Identify the function to get characters from the start

    The LEFT() function extracts a specified number of characters from the beginning of a string.

  2. Step 2: Check the syntax correctness

    LEFT(name, 3) correctly gets the first 3 characters. Other options either extract from the wrong side or have incorrect syntax.

  3. Final Answer:

    SELECT LEFT(name, 3) FROM table; -> Option B

  4. Quick Check:

    LEFT gets first chars = D [OK]
Hint: LEFT(string, n) extracts first n characters [OK]
Common Mistakes:
  • Using RIGHT() instead of LEFT() for first characters
  • Incorrect SUBSTRING syntax with FROM keyword
  • Using MID() which is not standard SQL
3. Given a table users with a column email, what will this query return?
SELECT LENGTH(TRIM(email)) FROM users;
medium
A. Length of email excluding leading and trailing spaces
B. Length of email including spaces
C. Number of rows in the users table
D. Email addresses converted to uppercase

Solution

  1. Step 1: Understand the TRIM() function

    TRIM(email) removes spaces at the start and end of the email string.

  2. Step 2: Understand LENGTH() function

    LENGTH() returns the number of characters in the string after trimming spaces.

  3. Final Answer:

    Length of email excluding leading and trailing spaces -> Option A

  4. Quick Check:

    TRIM removes spaces, LENGTH counts chars = A [OK]
Hint: TRIM removes spaces, LENGTH counts characters [OK]
Common Mistakes:
  • Thinking LENGTH counts spaces inside the string
  • Confusing TRIM with UPPER or LOWER
  • Assuming query counts rows, not string length
4. Identify the error in this SQL query that tries to find names starting with 'Jo':
SELECT * FROM employees WHERE name LIKE 'Jo%';
medium
A. LIKE is case-sensitive and may miss 'jo' lowercase
B. The wildcard % should be inside quotes
C. Missing a semicolon at the end
D. Using = instead of LIKE for pattern matching

Solution

  1. Step 1: Check the LIKE pattern usage

    The pattern 'Jo%' correctly uses % wildcard inside quotes to match names starting with 'Jo'.

  2. Step 2: Consider case sensitivity of LIKE

    LIKE is case-sensitive in many SQL systems, so it won't match 'jo' lowercase names, which may cause missed results.

  3. Final Answer:

    LIKE is case-sensitive and may miss 'jo' lowercase -> Option A

  4. Quick Check:

    LIKE case sensitivity can cause misses = A [OK]
Hint: LIKE may be case-sensitive; use UPPER() to fix [OK]
Common Mistakes:
  • Thinking % wildcard must be outside quotes
  • Ignoring case sensitivity in LIKE
  • Assuming semicolon is mandatory for all SQL engines
5. You want to combine first name and last name columns into one full name column with a space between them. Which SQL expression correctly does this?
hard
A. SELECT first_name || ' ' || last_name AS full_name FROM users;
B. SELECT CONCAT(first_name, last_name) AS full_name FROM users;
C. SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
D. SELECT first_name + ' ' + last_name AS full_name FROM users;

Solution

  1. Step 1: Understand string concatenation in SQL

    Different SQL systems use different ways to join strings: || or CONCAT(). The CONCAT() function can take multiple arguments.

  2. Step 2: Check which option adds space between names

    SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; uses CONCAT(first_name, ' ', last_name), correctly adding a space between first and last names. SELECT first_name || ' ' || last_name AS full_name FROM users; uses || which works in some systems but not all. SELECT CONCAT(first_name, last_name) AS full_name FROM users; misses the space. SELECT first_name + ' ' + last_name AS full_name FROM users; uses + which is invalid for strings in SQL.

  3. Final Answer:

    SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; -> Option C

  4. Quick Check:

    CONCAT with space joins names correctly = C [OK]
Hint: Use CONCAT with space to join names safely [OK]
Common Mistakes:
  • Forgetting space between names
  • Using + operator for string concatenation
  • Using CONCAT without space argument
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard