MySQL string functions operate on text values to join, inspect, extract, search, and modify strings. Common examples include CONCAT() for combining values, SUBSTRING() for extracting part of a value, and TRIM() for removing surrounding spaces; the right function depends on whether you need to change text, compare it, or measure it.
What MySQL string functions do
A string function takes one or more values and returns a result, often another string. You can use these functions in a SELECT list, a WHERE condition, an ORDER BY expression, or an update. They are useful for displaying names consistently, parsing identifiers, cleaning imported data, and finding text embedded in a column.
For example, this query combines a first and last name for display:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
The result is calculated when the query runs; it does not change the stored values. To permanently alter stored data, use an appropriate UPDATE statement, and consider testing the expression with SELECT first. MySQL’s available functions and some details can vary by version, so check the documentation for the server version in use when relying on newer features.
Joining strings with CONCAT and CONCAT_WS
CONCAT() combines its arguments in order. Arguments can include columns, literals, or expressions. MySQL converts non-string arguments to strings where appropriate, but explicit conversion can make complicated expressions easier to understand. A practical example that builds a label is SELECT CONCAT('Order #', order_id) AS label FROM orders;.
A key behavior to remember is that CONCAT() returns NULL if any argument is NULL. If a customer has no middle name, for example, directly concatenating the first, middle, and last name may produce NULL for the entire result. You can choose how to handle missing values with COALESCE() or use CONCAT_WS().
CONCAT_WS(separator, value1, value2, ...) means concatenate with separator. It places the separator between the non-NULL values after the separator argument. This is convenient for names or address parts:
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM customers;
Here, a NULL middle name is skipped rather than making the entire result NULL. Empty strings are not the same as NULL, however: an empty value is included and can leave extra separators. Also, if the separator itself is NULL, CONCAT_WS() returns NULL.
Extracting pieces of a string
SUBSTRING(), also written SUBSTR(), extracts a portion of a string. Positions start at 1, not 0. For example, SELECT SUBSTRING('database', 1, 4); returns data. The third argument is the number of characters to return. If it is omitted, the function returns the remainder of the string from the chosen position.
MySQL also accepts a negative starting position for SUBSTRING(), counting back from the end. Thus SELECT SUBSTRING('database', -4); returns base. This can help with values that have a predictable suffix, but it is fragile if the input format is inconsistent. For a fixed number of characters at either end, LEFT(string, length) and RIGHT(string, length) are usually easier to read.
When a value is separated by a known delimiter, SUBSTRING_INDEX() can return text before or after a specified occurrence. For example, SELECT SUBSTRING_INDEX('east|west|north', '|', 2); returns east|west. A negative count starts counting delimiters from the right, so a count of -1 returns the part after the final delimiter. This is useful for simple, consistently formatted values, but it is not a full parser for quoted CSV, nested data, or delimiters that can appear inside a field.
Searching and replacing text
Use LOCATE() to find the position of one string inside another: LOCATE(substring, string). Its result is 1-based, and it returns 0 if the substring is not found. For example, SELECT LOCATE('@', 'reader@example.com'); returns 7. An optional third argument sets where the search begins. INSTR(string, substring) performs a similar search with its arguments in the opposite order.
To test whether text appears in a value, a condition such as LOCATE('error', message) > 0 is often clear. The LIKE operator is another common choice for pattern matching, but it is an operator rather than a string function. A pattern such as message LIKE '%error%' searches for the text anywhere in the value. A leading wildcard can make an ordinary index less useful for that search, which matters on large tables.
REPLACE(string, from_string, to_string) substitutes every occurrence of the target text. For instance, SELECT REPLACE('2026/11/03', '/', '-'); returns 2026-11-03. The function does not update a table by itself. Also distinguish it from the SQL statement REPLACE INTO, which concerns inserting or replacing rows. String REPLACE() is case-sensitive, so matching cat does not necessarily replace Cat.
Cleaning and formatting text
TRIM() removes specified characters from the ends of a string; without a character specification, it removes spaces. LTRIM() removes leading spaces and RTRIM() removes trailing spaces. For example, SELECT TRIM(' North '); returns North. These functions do not remove matching characters from the middle of a value. MySQL’s TRIM() syntax can also specify LEADING, TRAILING, or BOTH and a removal string, which is useful when cleaning a known marker.
LOWER() and LCASE() convert letters to lowercase; UPPER() and UCASE() convert them to uppercase. These are useful for standardized display or for creating a normalized value in a query. They do not necessarily settle every question of language-aware comparison: character sets and collations affect how text is interpreted and compared. For reliable searching, choose a suitable collation rather than assuming that changing letter case handles every language or accent.
LPAD() and RPAD() add repeated padding characters on the left or right until a target length is reached. For example, SELECT LPAD('42', 5, '0'); returns 00042. These functions can create fixed-width display strings, but padding an identifier for presentation is different from changing its numeric value. If the original input is already longer than the requested length, the result is truncated to that length, so choose the target carefully.
FORMAT() is a special case: it formats a number with grouping separators and a chosen number of decimal places, returning a string. It is intended for display, not for calculations or storage as a numeric value. Its separators can depend on the locale argument. Keep numeric values numeric for arithmetic and sorting, and apply presentation formatting where the result is meant for people to read.
String length, character sets, and collations
LENGTH() and OCTET_LENGTH() report the number of bytes in a string. CHAR_LENGTH() and CHARACTER_LENGTH() report the number of characters according to the string’s character set. These values can differ: a character encoded with multiple bytes takes more than one byte, even though it is one character. For example, the byte length of a string containing accented or other non-ASCII characters may exceed its character length.
This distinction matters when checking column limits, sizing data for transfer, and interpreting user input. A character count is not always the same as the number of visible symbols: some displayed symbols are made from multiple Unicode code points, such as a base letter plus a combining mark or an emoji sequence. Database character functions should not be treated as a universal measure of what a person perceives as one symbol.
Character sets define how text is encoded, and collations define rules used for comparisons and sorting, including aspects such as case and accent sensitivity. Two strings that look similar may compare differently under different collations. Conversely, a case-insensitive collation can treat different letter cases as equivalent in a comparison. Check the column’s character set and collation when search or sort behavior seems surprising.
NULL values, query use, and common mistakes
Many string functions return NULL when given a NULL input, though exact behavior depends on the function. This can affect a whole expression: a WHERE comparison involving NULL does not evaluate to true in the ordinary way. Use IS NULL to test for missing values, and decide deliberately whether to preserve a missing value or replace it with a fallback such as COALESCE(column_name, '').
A frequent mistake is using a function on an indexed column in a filter and expecting the same index behavior as a direct comparison. For example, WHERE LOWER(email) = 'a@example.com' applies a function to each candidate value and may prevent a conventional index on email from being used as efficiently. Depending on the schema and MySQL version, a suitable collation, a generated column, or a functional index may be an option. Check the execution plan with EXPLAIN rather than assuming a function is free or that an index will be used.
Another common confusion is treating a string function as a data-cleaning guarantee. Trimming spaces does not validate an email address, convert every Unicode form to a canonical form, or repair malformed encodings. Functions transform data according to their defined rules; validation and business rules need their own checks. For bulk updates, preview the expression with a SELECT, back up important data, and test edge cases such as NULL, empty strings, and unexpected delimiters.
Choosing a function and using regular expressions
Pick the simplest function that expresses the actual task. Use CONCAT_WS() for joining optional values with a separator, SUBSTRING() for positions and lengths, SUBSTRING_INDEX() for a predictable delimiter, and LOCATE() when you need a position. Use TRIM() for edge cleanup, and use REPLACE() for literal substitutions. These choices make a query easier to review and can avoid the extra complexity of a pattern language.
For patterns more complex than a literal search, MySQL 8.0 provides regular-expression functions including REGEXP_LIKE(), REGEXP_SUBSTR(), and REGEXP_REPLACE(). For example, REGEXP_REPLACE(code, '[^0-9]', '') removes characters that are not digits from a value in a query result. Regular expressions can express flexible patterns, but they take more care to write and can be harder to maintain than straightforward string functions. Regular-expression support and behavior differ across MySQL releases; consult the manual for the exact version, especially if compatibility with older servers matters.
Before choosing an approach, consider:
- Whether the result is for display, filtering, or permanent data modification.
- Whether missing values should remain
NULLor become empty strings. - Whether the data uses a predictable delimiter and whether that delimiter can occur inside the content.
- Whether byte length, character length, collation, or case sensitivity affects the result.
- Whether the expression runs against many rows and should be checked with
EXPLAIN.
Frequently Asked Questions
What is the most commonly used string function in MySQL?CONCAT() is a common choice for combining values, while SUBSTRING(), TRIM(), and REPLACE() are also widely useful. The best function depends on the task; for concatenating values where some may be NULL, CONCAT_WS() may be more convenient.
What is the difference between LENGTH() and CHAR_LENGTH() in MySQL?LENGTH() counts bytes, while CHAR_LENGTH() counts characters according to the character set. For text with multibyte characters, the byte count can be larger. Neither necessarily counts user-perceived symbols such as complex emoji as one unit.
How do I remove spaces from a MySQL string?
Use TRIM(value) to remove spaces from both ends, LTRIM(value) for the beginning, or RTRIM(value) for the end. These functions leave spaces inside the text alone. To remove or replace internal spaces, use a different expression, such as REPLACE() for a literal space.
How can I get text after a delimiter in MySQL?
For a predictable delimiter, use SUBSTRING_INDEX(value, delimiter, -1) to return the text after the final occurrence. If you need the part after a particular occurrence rather than the last one, combine string functions carefully or use a regular expression where the server version supports it.
Does CONCAT() return an empty string when a value is NULL?
No. In MySQL, CONCAT() returns NULL if any argument is NULL. Use COALESCE() to supply a fallback, or consider CONCAT_WS(), which skips NULL values after its separator argument.
Related
- ZP Balance of Payments: Meaning and Key Concepts
- Assembly Split in CATIA: How to Separate a Product
- Balance Sheet Accounting: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- SUMPRODUCT in Excel: What It Contains and How It WorksExcel’s SUMPRODUCT contains one required array argument and optional additional arrays; it multiplies corresponding entries across the arrays,…
- Excel Index Match Multiple Criteria Non Array?Yes, you can use INDEX and MATCH with multiple criteria in Excel without entering it as an array…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
- Excel Formula to Calculate Total Hours WorkedTo calculate hours worked in Excel, subtract the start time from the end time and multiply by 24:…
- Excel SUMPRODUCT Sample Data: How to Use It in FormulasExcel’s SUMPRODUCT multiplies corresponding values in ranges and adds the results, so it can calculate totals such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.