String Functions

These examples use MySQL string functions and collation behaviour. TRIM normally removes pad spaces unless told to remove another string, and applying LOWER() to an indexed column may prevent use of an ordinary index.

Introduction

What is it? String functions are built-in SQL tools that let you clean up, reshape, search inside, or combine text data. Instead of pulling raw text out of a database and fixing it in another programming language, SQL lets you do most of that work directly inside a query.

Why do we need it? Real-world text data is almost never clean. The same word can show up as “Apple,” “apple,” and “APPLE” in different rows. Names might have extra spaces from a form field. Emails might be typed with inconsistent casing. If you compare, search, or group this messy text without cleaning it first, your results silently become wrong, not because SQL is broken, but because the text itself doesn’t match the way you’d expect.

Real-world analogy Think of a librarian sorting book titles. If some books are labeled “The Great Gatsby” and others “the great gatsby,” a librarian who only sorts by exact matching would treat these as two completely different books, even though they’re the same. String functions are the librarian’s way of standardizing every label before sorting or comparing them, so nothing gets missed due to a formatting difference.

Business use case A common example is matching customer emails during signup. If one system stores “Raj@gmail.com” and another stores “raj@gmail.com,” a plain equality check might treat them as different people. Using a function like LOWER() before comparing ensures both are treated the same, avoiding duplicate accounts or missed matches.

Dataset Used

Most examples in this document work directly with plain string literals (like 'Hello' or 'RAJ') rather than a full table, since string functions are usually demonstrated on the values themselves first, before being applied to real columns.

Where a table is useful, this sample structure is used:

CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL,
  quantity INT DEFAULT NULL
) ENGINE=InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

The important part of this table definition isn’t just the columns, it’s the collation setting at the end, which controls how text in this table gets compared and sorted. This becomes important once you start comparing or matching strings, not just formatting them.

Syntax

There isn’t one single syntax for “string functions,” since this topic covers many different tools. Here’s a quick reference table of everything covered in this document:

Every one of these is used inside a normal SELECT statement, either directly on a literal value, or on a column from a table.

Mental Model

Think of raw text in a database as clay that hasn’t been shaped yet. String functions are your different tools for working with that clay:

• CONCAT / CONCAT_WS — glue, for sticking pieces of text together
• LOWER / UPPER — a mold that presses everything into the same case, so two differently-cased pieces come out looking identical
• TRIM / LTRIM / RTRIM — a trimming knife, cutting off unwanted spaces from the edges
• LENGTH / CHAR_LENGTH — a measuring tape, though one measures in “bytes” and the other in “visible characters,” which can give different readings for the same piece of text
• LEFT / RIGHT / SUBSTRING — a cutting tool, slicing out just the piece of text you need
• LOCATE / INSTR — a searchlight, telling you exactly where inside the text a specific piece is hiding
• REPLACE — a find-and-swap tool, replacing every instance of one substring with another
• LIKE — a stencil with holes cut into it (the wildcards), letting only text matching that shape pass through

Keeping this mental picture in mind makes it easier to remember which tool to reach for, based on what you’re actually trying to do to the text.

How SQL Executes It

Most string functions are evaluated left to right, function by function, exactly as written, similar to how a calculator processes a math expression.

For a query like:

SELECT UPPER(TRIM(' raj '));

SQL executes this from the inside out:

1. First, TRIM(' raj ') runs, removing the leading and trailing spaces, producing 'raj'.
2. Then, UPPER('raj') runs on that result, producing 'RAJ'.

This “inside-out” execution order matters a lot once you start nesting multiple string functions together, which is extremely common in real cleaning queries.

For comparisons (like = or LIKE), SQL also relies on the column or literal’s collation to decide whether two strings are considered equal. This happens automatically in the background, based on how the column (or the whole database) was set up.

Example 1 — Understanding Collation Before Anything Else

Problem Before touching any string function, it helps to see directly how collation affects whether two strings are considered “equal.”

Collation flags reference

Explanation A collation like utf8mb4_0900_ai_ci (used in the sample sales table) combines two of these flags together: ai (accent-insensitive) and ci (case-insensitive). This means, under this specific collation, 'Apple' and 'apple' would be treated as equal, and 'cafe' and 'café' would also be treated as equal, purely because of how this collation was configured, not because of anything you wrote in your query.

Why it works This is the reason two strings that look different in a spreadsheet can still return TRUE when compared with = in SQL. The comparison isn’t just “character by character,” it’s “character by character, according to the collation’s rules.” This single idea explains a huge number of confusing SQL behaviors around text matching.

Example 2 — CONCAT() vs CONCAT_WS()

Problem Combine a first name and last name into a single greeting, and see what happens when one of the pieces is missing (NULL).

SQL Query

SELECT CONCAT('Hello', ' ', 'Raj');
-- Output: Hello Raj

SELECT CONCAT('Hello', NULL, 'Raj');
-- Output: NULL

SELECT CONCAT_WS(' ', 'Hello', NULL, 'Raj');
-- Output: Hello Raj

SELECT CONCAT_WS('. ', NULL, 'Hi', 'Raj');
-- Output: Hi. Raj

ExplanationCONCAT() joins every piece exactly as given, but if even one piece is NULL, the entire result becomes NULL, since NULL tends to “poison” any expression it touches unless handled specially. CONCAT_WS() (With Separator) behaves differently: it automatically skips over NULL values, and only places the separator between the actual non-NULL pieces that remain.

Output

Why it works This is one of the most common real-world bugs in reporting queries: someone builds a “full name” column using CONCAT(first_name, ' ', last_name), and any customer with a missing last name suddenly shows a completely blank full name, instead of just their first name. CONCAT_WS() avoids this trap entirely.

Example 3 — LOWER() and UPPER() for Standardizing Text

Problem Standardize inconsistent text casing so it can be compared or displayed uniformly.

SQL Query

SELECT LOWER('RAjstriveR');
-- Output: rajstriver

SELECT UPPER('RAjstriveR');
-- Output: RAJSTRIVER

ExplanationLOWER() converts every letter to lowercase, and UPPER() converts every letter to uppercase, regardless of how the original text was typed.

Output

Why it works This is extremely useful before comparing two pieces of text that should be treated as the same, even if their casing differs, especially in databases where the collation is case-sensitive by default and won’t automatically treat “Apple” and “apple” as equal.

Example 4 — TRIM(), LTRIM(), and RTRIM() for Cleaning Whitespace

Problem Remove extra, unwanted spaces from user-submitted or imported text.

SQL Query

SELECT TRIM('  RAJ  ');
-- Output: RAJ

SELECT LTRIM(' RAJ ');
-- Output: RAJ  (only the leading space is removed, trailing space stays)

SELECT RTRIM(' RAJ ');
-- Output:  RAJ  (only the trailing space is removed, leading space stays)

ExplanationTRIM() removes whitespace from both the beginning and the end of a string. LTRIM() removes it only from the left (start), and RTRIM() removes it only from the right (end). None of these three functions touch spaces that appear in the middle of the text.

Output

Why it works This is one of the most common cleanup steps for data coming from CSV imports or web forms, where users often accidentally add leading or trailing spaces without realizing it. Left unaddressed, a value like ' Raj' will not equal 'Raj' in an exact string comparison, causing silent mismatches.

Example 5 — LENGTH() vs CHAR_LENGTH()

Problem Measure the length of different kinds of text, including plain letters, accented characters, and emojis, and see how the two length functions disagree.

SQL Query

SELECT LENGTH('RAJ'), CHAR_LENGTH('RAJ');
-- LENGTH: 3, CHAR_LENGTH: 3

SELECT LENGTH('é'), CHAR_LENGTH('é');
-- LENGTH: 2, CHAR_LENGTH: 1

SELECT LENGTH('😀'), CHAR_LENGTH('😀');
-- LENGTH: 4, CHAR_LENGTH: 1

ExplanationLENGTH() counts the number of bytes used to store the string, while CHAR_LENGTH() counts the number of actual visible characters. For plain English letters, these two numbers are usually the same, since most basic characters take up exactly 1 byte each in common encodings. But accented characters and emojis often take up more than 1 byte internally (this depends on the character encoding being used, such as UTF-8), even though they still count as just 1 visible character to a human reading the text.

Output

Why it works This distinction matters a lot when validating things like “maximum 10 characters allowed” on a form. If you check that limit using LENGTH() instead of CHAR_LENGTH(), a name containing accented letters or emojis might get rejected even though it visually looks well within the character limit, simply because it uses more storage bytes than plain ASCII text would.

Example 6 — LEFT(), RIGHT(), and SUBSTRING() for Slicing Text

Problem Extract specific portions of a username, like a short prefix, a fixed suffix, or a custom middle slice.

SQL Query

SELECT LEFT('plus_user', 3);
-- Output: plu

SELECT RIGHT('plus_user', 4);
-- Output: user

SELECT SUBSTRING('plus_user', 3);
-- Output: us_user

SELECT SUBSTRING('plus_user', 3, 2);
-- Output: us

ExplanationLEFT(str, n) grabs the first n characters from the start. RIGHT(str, n) grabs the last n characters from the end. SUBSTRING(str, start, len) is the most flexible option, it starts at a specific position (counting from 1, not 0) and grabs a specific number of characters from there. If you leave out the length, it grabs everything from that starting position to the end of the string.

Output

Why it works This is very useful for extracting known-format pieces of data, like the first 3 letters of a product code, the last 4 digits of a phone number, or a fixed-width segment from an old legacy data format. Remember, MySQL uses 1-based indexing, meaning position 1 is the very first character, not position 0.

Example 7 — LOCATE() and INSTR() for Finding Substrings

Problem Find the position of the @ symbol inside an email address, to later split it into username and domain.

SQL Query

SELECT LOCATE('@', 'Raj@gmail.com');
-- Output: 4

SELECT INSTR('Raj@gmail.com', '@');
-- Output: 4

SELECT INSTR('Raj@gmail.com', '7');
-- Output: 0

Explanation Both LOCATE() and INSTR() do the same job, finding where a substring first appears inside a larger string, but their arguments are in a different order. LOCATE(substr, str) takes the substring first, then the full string. INSTR(str, substr) takes the full string first, then the substring. Both use 1-based indexing, and both return 0 if the substring is not found anywhere in the string.

Output

Why it works This 0-means-not-found behavior is important to remember, since 0 is a valid, meaningful result (unlike NULL), and forgetting to check for it can cause a query to accidentally treat “not found” as if it found something at position 0, which doesn’t actually exist in 1-based indexing.

Example 8 — REPLACE() for Swapping Substrings

Problem Convert an underscore-separated string into a hyphen-separated one, commonly needed when reformatting slugs, filenames, or codes.

SQL Query

SELECT REPLACE('a_b_c', '_', '-');
-- Output: a-b-c

ExplanationREPLACE(str, old, new) scans through the entire string and swaps out every occurrence of the old substring with the new one, not just the first occurrence.

Output

Why it works Because REPLACE affects every matching occurrence, it’s a quick way to reformat entire strings in one pass, without needing a loop or multiple separate function calls. This is commonly used for things like converting old file naming conventions, cleaning up inconsistent delimiters, or masking sensitive characters in logs.

Example 9 — LIKE Pattern Matching with Wildcards

Problem Check if a username follows a specific pattern, using wildcard characters instead of an exact match.

SQL Query

SELECT 'plus_user' LIKE 'plus%';
-- Output: 1 (true)

SELECT 'plus1user' LIKE 'plus_us_r';
-- Output: 1 (true)

SELECT 'plus1user' LIKE 'plus\_user';
-- Output: 0 (false)

ExplanationLIKE supports two wildcard characters: % matches any number of characters (including zero), and _ matches exactly one character. So 'plus%' matches anything starting with “plus,” regardless of what comes after. 'plus_us_r' matches “plus,” then any single character, then “us,” then any single character, then “r,” which happens to match “plus1user” because the underscores line up with “1” and “e.”

The third example uses an escaped underscore (\_), which tells SQL to treat that underscore as a literal character, not a wildcard. Since “plus1user” doesn’t literally contain “plus_user” with a real underscore in that position, it returns false.

Output

Why it works This is the foundation of flexible text searching in SQL, useful for things like “find all emails ending in @gmail.com” (LIKE '%@gmail.com') or “find all product codes starting with A, followed by exactly 3 digits” (LIKE 'A___'). Escaping wildcards is important whenever your actual search text might contain a literal % or _ character, though the exact escape behavior can depend on the SQL mode or database being used.

Deep Dive

Internal behavior String functions generally process text based on the character set and collation assigned to the column (or the literal value’s default collation, if no column is involved). This is why the exact same function call can behave slightly differently across two databases with different default collations, even though the SQL syntax looks identical.

NULL handling Most string functions propagate NULL, meaning if any input to the function is NULL, the entire result becomes NULL. CONCAT() is a classic example of this. CONCAT_WS() is a deliberate exception, specifically designed to skip NULLs instead of collapsing the whole result.

Performance considerations Applying a string function (like LOWER()) directly to a column inside a WHERE clause (for example, WHERE LOWER(name) = 'raj') can prevent the database from using an index efficiently on that column, since the database may need to compute the function’s result for every single row before it can compare it, rather than doing a fast, direct index lookup. When performance matters on large tables, it’s often better to store already-normalized text (like a lowercase version of a name) in its own column, and index that instead.

Database behavior Function names and exact behavior can vary between databases. MySQL uses LOCATE(), INSTR(), CONCAT_WS(), and 1-based indexing throughout. PostgreSQL has similar functions but sometimes with different names or slightly different argument orders (for example, PostgreSQL uses POSITION(substring IN string) in addition to its own version of similar functions). Always double check the exact syntax for the specific database being used.

Real-world Use Cases

• Data cleaning pipelines: Standardizing casing and trimming whitespace before loading data into a reporting table, similar to Examples 3 and 4
• Email and username validation: Using LOWER() before comparing emails, since email addresses are typically treated as case-insensitive by most systems
• Building display names: Using CONCAT_WS() to safely combine first name, middle name, and last name, even when some of those fields might be missing
• Extracting structured pieces from unstructured text: Using SUBSTRING(), LOCATE(), or INSTR() to pull out domains from email addresses, area codes from phone numbers, or specific segments from product codes
• Search and filtering features: Using LIKE with wildcards to power “search as you type” features or partial-match filters in reporting dashboards
• Format standardization: Using REPLACE() to convert between different delimiter styles, like underscores to hyphens in slugs or filenames

Review Tips

Interviewers commonly test string functions alongside these ideas:

• “Write a query to find all users whose email domain is gmail.com.” — Tests LIKE with wildcards, or a combination of LOCATE/SUBSTRING.
• “Why did my CONCAT() return NULL when I expected a partial result?” — Tests understanding of how CONCAT() handles NULL, versus CONCAT_WS().
• “What’s the difference between LENGTH() and CHAR_LENGTH(), and when would that difference actually matter?” — Tests understanding of byte-level versus character-level counting, especially with non-ASCII text.
• “How would you standardize inconsistent casing across a text column before comparing it?” — Tests knowledge of LOWER()/UPPER() and awareness of collation.
• “What does LIKE ‘A_B%’ actually match?” — Tests understanding of wildcard behavior (_ for exactly one character, % for any number of characters).

A strong answer usually mentions collation explicitly, since interviewers like to see that you understand string comparison isn’t just about the literal characters, it’s also about the rules governing how those characters get compared.

Common Mistakes to Avoid

Mistake 1: Assuming CONCAT() handles NULLs gracefully

Why it happens: It feels like joining text together should just skip over anything missing.

Wrong:

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM customers;

If middle_name is NULL for a customer, the entire full_name becomes NULL, even though first_name and last_name both had valid values.

Correct:

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM customers;

Mistake 2: Comparing text without normalizing case first

Why it happens: It’s easy to forget that a column might contain inconsistent casing, especially if the data comes from multiple sources or was manually entered.

Wrong:

SELECT * FROM users WHERE email = 'Raj@gmail.com';

If the database’s collation is case-sensitive, this would miss a row where the email was stored as 'raj@gmail.com'.

Correct:

SELECT * FROM users WHERE LOWER(email) = LOWER('Raj@gmail.com');

Mistake 3: Using LENGTH() when you actually mean CHAR_LENGTH()

Why it happens: LENGTH() is often the first function people learn, so it becomes the default choice without thinking about byte-versus-character differences.

Wrong:

SELECT * FROM users WHERE LENGTH(name) <= 10;

This can incorrectly reject names containing accented characters or emojis, even if they are visually well within a 10-character limit, since those characters can take up more than 1 byte each.

Correct:

SELECT * FROM users WHERE CHAR_LENGTH(name) <= 10;

Mistake 4: Forgetting that SQL uses 1-based indexing for string positions

Why it happens: Most general-purpose programming languages (like Python or JavaScript) use 0-based indexing, so it’s an easy habit to carry over by mistake.

Wrong (conceptual mistake): Assuming SUBSTRING('plus_user', 0, 3) grabs the first three characters, the same way it would in a 0-indexed language.

Correct: In MySQL, position 1 refers to the very first character. SUBSTRING('plus_user', 1, 3) is what correctly grabs the first three characters (“plu”).

Mistake 5: Not accounting for 0 as a valid, meaningful return value from LOCATE/INSTR

Why it happens: 0 can feel like it should mean “nothing” or “empty,” similar to how NULL behaves elsewhere in SQL.

Wrong:

SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM users;

If an email somehow doesn’t contain an @ at all, LOCATE() returns 0, and 0 - 1 becomes -1, which can produce unexpected or empty results from SUBSTRING, silently hiding a data quality problem instead of surfacing it clearly.

Correct: Always check explicitly for a 0 result from LOCATE/INSTR before using it in further calculations, for example with a CASE expression that handles the “not found” scenario separately.

Mistake 6: Forgetting that TRIM only removes spaces from the edges, not the middle

Why it happens: It’s easy to assume TRIM cleans up “all extra spaces” everywhere in the string.

Wrong assumption: Expecting TRIM('Raj Kumar') to collapse the multiple spaces in the middle down to one space.

Correct understanding: TRIM only removes leading and trailing whitespace. Cleaning up repeated spaces in the middle of a string typically requires REPLACE() or a more advanced regular expression function, depending on the database.

Key Takeaways

• Strings in SQL aren’t just raw characters, they’re compared and sorted according to collation rules, which can make two visually different strings count as equal, or two visually identical strings count as different, depending on accent-sensitivity and case-sensitivity settings
• CONCAT() breaks entirely (returns NULL) if any single argument is NULL, while CONCAT_WS() safely skips NULLs
• LOWER() and UPPER() are the standard tools for normalizing case before comparison or display
• TRIM(), LTRIM(), and RTRIM() only clean up spaces at the edges of a string, never in the middle
• LENGTH() counts bytes, CHAR_LENGTH() counts visible characters, and these two numbers can differ significantly for accented letters or emojis
• LEFT(), RIGHT(), and SUBSTRING() are your main tools for slicing out specific portions of text, and SQL uses 1-based indexing, not 0-based
• LOCATE() and INSTR() do the same job (finding a substring’s position) but take their arguments in a different order, and both return 0, not NULL, when nothing is found
• REPLACE() swaps every occurrence of a substring, not just the first one
• LIKE with % and _ wildcards enables flexible pattern-based searching, but escaping those wildcards is necessary if you need to match them as literal characters
• Applying string functions directly on a column inside a WHERE clause can prevent efficient index usage on large tables, so consider storing pre-normalized values separately if performance becomes a concern