Skip to content

Functions & Upserts

Transform data with string and date functions, and handle duplicates with upsert patterns.

Raw data often needs formatting: names need capitalizing, dates need reformatting, and multiple values need combining into one string. MySQL provides built-in functions for these tasks. You also need to handle duplicate rows gracefully when inserting data.

Definition

MySQL string functions transform text data. CONCAT joins strings, SUBSTRING extracts parts, LENGTH returns byte count, and UPPER/LOWER change case. INSERT ... ON DUPLICATE KEY UPDATE handles upsert patterns.

Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name, email) VALUES
  ('Alice', 'alice@school.edu'),
  ('Bob', 'bob@school.edu'),
  ('Charlie', 'charlie@school.edu');

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);

String Functions

MySQL provides functions to manipulate text: combining, extracting, transforming, and measuring strings.

CONCAT: combine strings

SELECT CONCAT(name, ' scored ', score) AS result
FROM students
JOIN enrollments ON students.id = enrollments.student_id;

CONCAT joins multiple strings into one. Here it combines a name, a literal string, and a score into a readable sentence.

UPPER, LOWER, LENGTH

SELECT UPPER(name) AS upper_name,
  LOWER(name) AS lower_name,
  LENGTH(name) AS name_length
FROM students;

UPPER converts to uppercase, LOWER to lowercase. LENGTH returns the number of bytes. Use CHAR_LENGTH for the number of characters (important for multi-byte encodings).

SUBSTRING: extract part of a string

SELECT SUBSTRING(name, 1, 3) AS first_three
FROM students;

SUBSTRING(str, start, length) extracts a portion. Start is 1-indexed. This gets the first 3 characters of each name.

TRIM: remove whitespace

SELECT TRIM('  Hello  ') AS trimmed;

TRIM removes leading and trailing spaces. Use LTRIM for leading only, RTRIM for trailing only.

Date Functions

MySQL has functions for getting the current date/time, adding intervals, calculating differences, and formatting dates.

Current date and time

SELECT NOW() AS current_datetime,
  CURDATE() AS today,
  CURTIME() AS current_time;

NOW() returns date and time. CURDATE() returns just the date. CURTIME() returns just the time.

DATE_ADD and DATE_SUB

SELECT
  DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
  DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

DATE_ADD adds an interval. DATE_SUB subtracts one. Intervals can be DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, and more.

DATEDIFF: difference between dates

SELECT DATEDIFF('2025-12-31', '2025-01-01') AS days_diff;

DATEDIFF returns the number of days between two dates. The first argument minus the second. Can be negative.

DATE_FORMAT: format dates

SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y') AS formatted;

DATE_FORMAT formats a date using specifiers. %W is weekday name, %M is month name, %d is day, %Y is 4-digit year. Output: 'Saturday, June 27, 2026'.

GROUP_CONCAT

GROUP_CONCAT aggregates multiple values from a group into a single comma-separated string. It is MySQL-specific and very useful for building lists.

GROUP_CONCAT

SELECT student_id,
  GROUP_CONCAT(course_name SEPARATOR ', ') AS courses
FROM enrollments
GROUP BY student_id;

For each student, combines all their course names into a comma-separated string. SEPARATOR defines the delimiter (default is comma).

Upsert Patterns

An upsert is an insert that handles duplicates gracefully. Instead of failing on a duplicate key, it updates the existing row or skips it. This is essential for applications that sync data from external sources.

INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO students (name, email)
VALUES ('Alice', 'alice@school.edu')
AS new_row
ON DUPLICATE KEY UPDATE email = new_row.email;

Inserts a new row. If the name or email already exists (UNIQUE constraint), it updates the email instead of failing. The AS new_row alias lets you reference the attempted values.

INSERT IGNORE

INSERT IGNORE INTO students (name, email)
VALUES ('Alice', 'alice@school.edu');

Inserts a new row. If a duplicate key exists, the INSERT is silently ignored. No error, no update. The row simply is not inserted.

REPLACE INTO

REPLACE INTO students (name, email)
VALUES ('Alice', 'alice@newschool.edu');

Inserts a new row. If a duplicate key exists, the old row is deleted and the new row is inserted. This fires the table's DELETE triggers (for the removed row) and INSERT triggers (for the new row).

When to use each:Upsert Comparison

ON DUPLICATE KEY UPDATE: Update specific columns when a duplicate exists. Most common pattern. Preserves the row ID.

INSERT IGNORE: Skip duplicates silently. Good for bulk imports where duplicates are expected.

REPLACE INTO: Delete and re-insert. Use when you need a fresh row. Triggers may fire. Changes the auto-increment ID.

What does UPPER('hello') return?

What does CONCAT('Hello', ' ', 'World') return?

What happens with INSERT IGNORE when a duplicate key exists?

Key Takeaways

  • CONCAT combines strings. UPPER, LOWER, LENGTH, SUBSTRING, TRIM transform text.
  • NOW(), CURDATE(), DATE_ADD, DATE_SUB, DATEDIFF, DATE_FORMAT handle dates.
  • GROUP_CONCAT aggregates values into a comma-separated string per group.
  • ON DUPLICATE KEY UPDATE inserts or updates on conflict (most common upsert).
  • INSERT IGNORE skips duplicates silently. REPLACE deletes and re-inserts.

Ready to test your knowledge?

Take a Quiz

Related Tutorials