String Functions

Text data rarely shows up exactly the way you want to present it. A username might need to be combined with other information, a review comment might be too long to display in full, or a search box needs to match "Inception" whether someone typed "inception", "INCEPTION", or just "incep". MySQL ships a set of built-in string functions that handle all of this directly inside a query, so you don't have to pull raw data out and reshape it in application code. We'll work through the most commonly used ones against StreamHub's users, movies, and reviews tables.

CONCAT() - Joining Strings Together

CONCAT() takes two or more arguments and glues them into a single string. It's the most-used string function simply because combining pieces of text is such a common need - here we build a display string that pairs a user's username with the year they signed up.

SELECT CONCAT(username, ' (since ', YEAR(signup_date), ')') AS display_name
FROM users;

Notice the numeric YEAR(signup_date) gets converted to a string automatically inside CONCAT() - you don't need to cast it yourself.

SUBSTRING(), LEFT() and RIGHT() - Extracting Part of a String

SUBSTRING() pulls out a portion of a string starting at a given position, for a given length. LEFT() and RIGHT() are shorthand versions that grab a fixed number of characters from one end. A common use case in StreamHub: showing only a preview of a long review comment rather than the whole thing in a list view.

SELECT id, SUBSTRING(comment, 1, 100) AS preview
FROM reviews;

-- LEFT() is a shorthand for grabbing from the start of the string
SELECT id, LEFT(comment, 100) AS preview
FROM reviews;

Both queries return the same result here - SUBSTRING(comment, 1, 100) and LEFT(comment, 100) both mean "the first 100 characters." RIGHT() works the same way but counts from the end, which is handy for things like showing only the last few digits of a reference code.

LENGTH() vs CHAR_LENGTH() - Bytes vs Characters

These two look interchangeable at first glance, but they measure genuinely different things. LENGTH() returns the number of bytes a string takes up, while CHAR_LENGTH() returns the number of characters. For plain ASCII text (English letters, digits, basic punctuation) these are always identical, because every character is exactly one byte. The difference only shows up with multi-byte Unicode text - and as covered in Creating & Managing Databases, StreamHub's tables use the utf8mb4 character set specifically so they can store emoji and non-English text correctly, which means this distinction matters here.

-- A review comment containing an emoji
SELECT LENGTH('Great movie 🎬') AS byte_length,
       CHAR_LENGTH('Great movie 🎬') AS char_length;

The emoji alone takes up 4 bytes under utf8mb4 but still counts as a single character, so CHAR_LENGTH() reports 14 characters while LENGTH() reports more bytes. If you ever need to validate something like "this comment must be at most 500 characters," reach for CHAR_LENGTH() - using LENGTH() on non-ASCII text would enforce the wrong limit.

UPPER() and LOWER() - Case Conversion

UPPER() and LOWER() convert a string to all-uppercase or all-lowercase. Beyond just display formatting, a very common pattern is using LOWER() on both sides of a comparison to do a case-insensitive-style match, so a search for "the matrix" still finds a movie stored as "The Matrix".

SELECT title FROM movies
WHERE LOWER(title) = LOWER('The Matrix');

Wrapping both the column and the search term in LOWER() means the comparison happens between two lowercase strings regardless of how either was originally typed or stored, so casing differences never cause a false miss.

TRIM() - Removing Leading/Trailing Whitespace

TRIM() strips whitespace from the start and end of a string (there's no way to fully prevent a stray space from being typed into a signup form, so cleaning it up in SQL is a practical safety net). Imagine a user submitted their username as " moviefan92 " with accidental extra spaces around it:

UPDATE users
SET username = TRIM(username)
WHERE id = 14;

After this runs, username becomes the clean "moviefan92" with no leading or trailing spaces. TRIM() only removes whitespace from the two ends of the string - it never touches spaces in the middle, so "movie fan 92" would stay exactly as it is.

REPLACE() - Substituting One Substring for Another

REPLACE() finds every occurrence of one substring inside a string and swaps it for another. A simple, common use: cleaning up inconsistent formatting in a comment, such as replacing every double space with a single space.

SELECT REPLACE(comment, '  ', ' ') AS cleaned_comment
FROM reviews
WHERE id = 203;

REPLACE() is case-sensitive and replaces every match it finds in the string, not just the first one - so if a comment had three double spaces, all three would become single spaces in the result.

LIKE with CONCAT() - Building a Dynamic Search Pattern

As covered in Operators & Conditions, LIKE matches a string against a pattern using % as a wildcard for "any characters." When the search term itself comes from a variable rather than being typed directly into the query, CONCAT() is the natural way to build that pattern on the fly:

SELECT title FROM movies
WHERE title LIKE CONCAT('%', 'inception', '%');

CONCAT('%', 'inception', '%') produces the pattern '%inception%', which means "any characters, then inception, then any characters" - this is the SQL equivalent of a "contains" search. In real application code the middle argument would normally be a variable holding whatever the user typed into a search box, with CONCAT() wrapping it in wildcards at query time.