✏️ Explanatory Question
Level: Intermediate — String functions are essential for cleaning, formatting, and searching text data.
String functions operate on character/text data and return a modified string or information about it. They are heavily used in data cleaning, reporting, and search features.
LENGTH() returns the number of bytes, while CHAR_LENGTH() returns the number of characters. For multi-byte text (like UTF-8 or Bengali), these differ — e.g., a Bengali character may use 3 bytes but count as 1 character.
| Function | Example | Result |
|---|---|---|
| CONCAT() | CONCAT('My', 'SQL') |
MySQL |
| UPPER() | UPPER('sql') |
SQL |
| SUBSTRING() | SUBSTRING('MySQL', 1, 2) |
My |
| REPLACE() | REPLACE('a-b-c', '-', '+') |
a+b+c |
| LEFT() | LEFT('Database', 4) |
Data |
| TRIM() | TRIM(' hi ') |
hi |
| LOCATE() | LOCATE('SQL', 'MySQL') |
3 |
-- Combine first and last name with a space
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM employees;
-- Standardize email to lowercase
SELECT LOWER(email) AS clean_email FROM users;
-- Extract area code from a phone number
SELECT LEFT(phone, 3) AS area_code FROM contacts;
-- Replace and trim in one query
SELECT TRIM(REPLACE(name, '_', ' ')) AS formatted_name FROM users;
-- Find position of '@' in an email
SELECT email, LOCATE('@', email) AS at_position FROM users;