Chapter 12: String, Date & Numeric Functions
These built-in functions are like your Swiss Army knife in MySQL. You will use them every single day to clean data, format reports, calculate ages, generate random values, manipulate text, and more.
We’ll cover three categories:
- String functions → Work with text
- Date/Time functions → Work with dates and times
- Numeric functions → Work with numbers
Let’s make sure we have some nice sample data in our students table (run this once):
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
TRUNCATE TABLE students; INSERT INTO students (full_name, phone, email, date_of_birth, gender, join_date, is_active, notes) VALUES (' Priya Sharma ', '9876543210', 'priya.sharma@gmail.com', '2008-05-12', 'F', '2025-01-05', TRUE, 'Very regular student'), ('Rahul Patel', '9123456789', 'rahul.p@gmail.com', '2007-11-20', 'M', '2025-01-10', TRUE, NULL), ('Sneha Joshi', '9988776655', 'sneha.joshi@yahoo.com', '2009-03-03', 'F', '2025-02-15', TRUE, 'Needs extra classes'), ('Aarav Khan', '9765432109', 'aarav.khan@outlook.com', '2006-08-15', 'M', '2025-03-01', TRUE, NULL), ('Kavya Iyer', '9898989898', 'kavya.iyer@gmail.com', '2010-04-22', 'F', '2025-03-10', TRUE, NULL); |
Now let’s dive in!
1. String Functions
(These help clean, format, and manipulate text)
| Function | What it does | Example Query | Example Output |
|---|---|---|---|
| CONCAT() | Joins strings together | SELECT CONCAT(full_name, ‘ – ‘, email) AS ‘Student Info’ FROM students; | Priya Sharma – priya.sharma@gmail.com |
| CONCAT_WS() | Joins with separator (ignores NULLs) | SELECT CONCAT_WS(' |
', full_name, phone, gender) FROM students; |
| UPPER() / LOWER() | Converts to uppercase/lowercase | SELECT UPPER(full_name), LOWER(email) FROM students; | PRIYA SHARMA , priya.sharma@gmail.com |
| TRIM() | Removes leading/trailing spaces | SELECT TRIM(full_name) AS CleanName FROM students; | Priya Sharma (no extra spaces) |
| SUBSTRING() / SUBSTR() | Extracts part of string | SELECT SUBSTRING(email, 1, 5) AS First5 FROM students; | priya , rahul , sneha , aarav , kavya |
| REPLACE() | Replaces substring | SELECT REPLACE(notes, ‘classes’, ‘help’) FROM students WHERE notes IS NOT NULL; | Needs extra help |
| LENGTH() / CHAR_LENGTH() | Counts characters (LENGTH includes bytes) | SELECT full_name, LENGTH(full_name) AS Len FROM students; | Priya Sharma → 18 |
Real-life example – Clean and format student names
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT TRIM(UPPER(full_name)) AS 'Clean Upper Name', CONCAT('Student: ', TRIM(full_name), ' (', LOWER(email), ')') AS 'Formatted Info' FROM students; |
2. Date & Time Functions
(Super useful for age calculation, reports, reminders)
| Function | What it does | Example Query | Example Output |
|---|---|---|---|
| NOW() | Current date + time | SELECT NOW(); | 2026-01-24 17:52:30 |
| CURDATE() / CURRENT_DATE() | Current date only | SELECT CURDATE(); | 2026-01-24 |
| CURTIME() | Current time only | SELECT CURTIME(); | 17:52:30 |
| DATE_FORMAT() | Formats date in any way | SELECT DATE_FORMAT(join_date, ‘%d-%b-%Y’) AS ‘Nice Date’ FROM students; | 05-Jan-2025 |
| DATEDIFF() | Days between two dates | SELECT full_name, DATEDIFF(CURDATE(), join_date) AS ‘Days Since Joined’ FROM students; | Priya Sharma → 384 days (approx) |
| DATE_ADD() / DATE_SUB() | Add/subtract days, months, years | SELECT DATE_ADD(join_date, INTERVAL 30 DAY) AS ‘Renewal Date’ FROM students; | 2025-02-04 (for Priya) |
| YEAR(), MONTH(), DAY() | Extract part of date | SELECT full_name, YEAR(date_of_birth) AS BirthYear FROM students; | 2008 |
Real-life example – Calculate approximate age and membership duration
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT full_name, DATE_FORMAT(date_of_birth, '%d %M %Y') AS 'Birth Date', YEAR(CURDATE()) - YEAR(date_of_birth) AS 'Age (approx)', CONCAT(DATEDIFF(CURDATE(), join_date), ' days') AS 'Member Since' FROM students; |
Bonus – Add 1 year to join date (for annual renewal reminder)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT full_name, join_date, DATE_ADD(join_date, INTERVAL 1 YEAR) AS 'Next Renewal' FROM students; |
3. Numeric Functions
(Useful for rounding money, random selection, absolute values)
| Function | What it does | Example Query | Example Output |
|---|---|---|---|
| ROUND() | Rounds to nearest value (can specify decimals) | SELECT ROUND(123.4567, 2); | 123.46 |
| CEIL() / CEILING() | Rounds up to next whole number | SELECT CEIL(4.1), CEIL(5.0); | 5, 5 |
| FLOOR() | Rounds down to previous whole number | SELECT FLOOR(4.9), FLOOR(5.0); | 4, 5 |
| ABS() | Absolute value (removes negative sign) | SELECT ABS(-50), ABS(50); | 50, 50 |
| RAND() | Random number between 0 and 1 | SELECT RAND(); | 0.723456… (different every time) |
Real-life examples:
A. Round fees (if we had a fees column)
SQL
|
0 1 2 3 4 5 6 |
SELECT ROUND(5999.789, 2) AS RoundedFees; |
B. Pick a random student (great for lucky draw)
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name FROM students ORDER BY RAND() LIMIT 1; |
C. Round age properly
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, FLOOR(DATEDIFF(CURDATE(), date_of_birth) / 365.25) AS 'Exact Age' FROM students; |
That’s it for Chapter 12! 🎉 You now have a powerful toolkit to clean text, format dates beautifully, calculate ages/durations, and handle numbers like a pro!
Homework for today (do it right now – very important!) Run these 5 queries and paste the output of each here:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- 1. Clean names and combine with email SELECT TRIM(UPPER(full_name)) AS CleanName, CONCAT('Email: ', LOWER(email)) AS Contact FROM students; -- 2. Format birth dates nicely and calculate approximate age SELECT full_name, DATE_FORMAT(date_of_birth, '%d %M %Y') AS BirthDate, YEAR(CURDATE()) - YEAR(date_of_birth) AS AgeApprox FROM students; -- 3. Days since joining and next renewal date SELECT full_name, DATEDIFF(CURDATE(), join_date) AS DaysJoined, DATE_ADD(join_date, INTERVAL 1 YEAR) AS NextRenewal FROM students; -- 4. Length of names and notes (where exists) SELECT full_name, LENGTH(full_name) AS NameLength, LENGTH(notes) AS NotesLength FROM students; -- 5. Pick 2 random students SELECT full_name FROM students ORDER BY RAND() LIMIT 2; |
