MySQL string functions transform, measure, search, and combine text; string operators compare text or match patterns. For example, use CONCAT(first_name, ' ', last_name) to join names, SUBSTRING(text, 1, 5) to extract characters, and LIKE 'Ann%' to find values beginning with Ann. The right choice depends on what you need to do and on how MySQL handles NULL values, character sets, and collations.
What MySQL considers a string
A string is text stored in a character column, such as CHAR, VARCHAR, or a text type, or supplied as a quoted literal. MySQL also supports binary strings, which represent bytes rather than text interpreted through a character set. That distinction matters: text comparisons can follow a collation’s rules for case and accents, while binary comparisons distinguish byte sequences.
String expressions can combine columns, literals, and function results. For instance, CONCAT(city, ', ', country) produces a display value from two columns and a separator. You can use such expressions in a SELECT list, but they also appear in filters, sorting, grouping, and data updates. When the expression changes the value, keep in mind that the result may not behave like the original indexed column.
Joining strings safely
CONCAT() joins its arguments in order. With first_name set to Ada and last_name set to Lovelace, CONCAT(first_name, ' ', last_name) returns Ada Lovelace. MySQL also provides CONCAT_WS(separator, value1, value2, ...), which inserts the separator between values. For example, CONCAT_WS(', ', city, region, country) is handy for forming a comma-separated location.
NULL behavior is an important difference. In MySQL, CONCAT() returns NULL if any argument is NULL. CONCAT_WS() skips NULL values after its separator, but it does not skip empty strings; if the separator itself is NULL, the result is NULL. For a name with a potentially missing middle name, the separator-based version can avoid a NULL result, but check whether empty fields should also be omitted.
Do not assume that + joins strings. It is an arithmetic operator, so MySQL may convert string values to numbers when evaluating an expression such as '12' + '3'. Also, || means logical OR by default. It acts as string concatenation only when the PIPES_AS_CONCAT SQL mode is enabled. Explicitly using CONCAT() makes intent clearer and avoids dependence on a session setting.
Extracting, measuring, and finding text
For extracting part of a string, use SUBSTRING() or its synonym SUBSTR(). Positions start at 1, not 0: SUBSTRING('database', 1, 4) returns data. A negative starting position counts backward from the end. LEFT(text, count) and RIGHT(text, count) are convenient when you need a fixed number of characters from either end. SUBSTRING_INDEX(text, delimiter, count) instead returns text before or after a chosen number of delimiter occurrences.
Choose the length function according to what you are measuring. CHAR_LENGTH(text) counts characters, whereas LENGTH(text) counts bytes. A multibyte character in a UTF-8 string can take more than one byte, so the results may differ. Even character counts do not necessarily equal the number of visible symbols: a displayed symbol can be made up of multiple Unicode code points.
To search for a substring, LOCATE(needle, haystack) returns its 1-based position, or 0 if it is not found; an optional third argument sets where the search starts. INSTR(haystack, needle) performs the same basic search with the arguments in the opposite order. For example, LOCATE('@', email) can help identify rows containing an at sign, though it is not a substitute for validating an email address.
Changing and cleaning string values
LOWER() and UPPER() change letter case, and REPLACE(text, from_text, to_text) replaces occurrences of a substring. For example, REPLACE(phone, '-', '') removes hyphens from a phone-number string. MySQL’s REPLACE() matching is case-sensitive, so a search string with different letter case may not match. Case conversion and comparisons can also depend on the character set and collation.
TRIM() removes spaces from both ends by default; LTRIM() and RTRIM() target one side. You can specify a removal string with syntax such as TRIM('/' FROM path). This removes the specified sequence from the ends, not every occurrence inside the value. Default trimming is not a universal cleanup for every whitespace character, so test your data if it may contain tabs, line breaks, or nonbreaking spaces.
LPAD() and RPAD() add padding to reach a requested length, which can be useful for display formats such as a fixed-width code. Be careful: if the requested length is shorter than the input, these functions truncate the input to that length. Formatting functions are best used with awareness of the desired output and should not silently discard significant data.
Comparisons and pattern-matching operators
Operators such as =, <>, and < compare strings, while LIKE checks a pattern. The comparison rules for text are generally governed by its collation. A case-insensitive collation may treat Blue and blue as equal; an accent-insensitive collation may treat accented and unaccented forms as equivalent. The exact behavior depends on the collation in use, so do not infer it from how values look on screen.
In a LIKE pattern, % matches any sequence of characters, including an empty sequence, and _ matches one character. Thus, name LIKE 'Ann%' finds values beginning with Ann, while code LIKE 'A_7' matches a three-character value beginning with A and ending with 7. If a literal percent sign or underscore is needed, escape it using an escape character and the ESCAPE clause, rather than allowing it to act as a wildcard.
REGEXP (also written RLIKE) performs regular-expression matching, which supports more complex patterns than LIKE. For example, name REGEXP '^[A-Z]' checks for a value beginning with an uppercase ASCII letter. Regular-expression syntax and collation behavior differ from simple wildcard matching, so use it when the pattern genuinely requires it, and consult the documentation for the MySQL version in use. Do not confuse LIKE wildcards with regular-expression symbols.
Character sets, collations, and NULL results
A character set determines how text is represented, and a collation defines rules for comparing and sorting text. MySQL supports many character sets and collations; utf8mb4 is commonly used for broad Unicode support. A column’s collation can affect equality tests, ordering, LIKE matches, and some string operations. If a comparison needs different rules, an explicit collation can be applied where appropriate, but make sure both sides are compatible and that the chosen rules reflect the application’s requirements.
NULL is not an ordinary string and generally means that a value is missing or unknown. Comparing a value to NULL with = NULL does not test for NULL; use IS NULL or IS NOT NULL. String functions often propagate NULL, though functions such as CONCAT_WS() handle NULL arguments specially. A filter such as WHERE CONCAT(first_name, last_name) = 'AdaLovelace' will not match rows where either input makes the concatenation NULL. Check for missing values explicitly or use a function suited to the intended behavior.
Choosing a function and avoiding performance surprises
Start with the simplest operation that expresses the task: CONCAT() for joining, SUBSTRING() for extraction, LOCATE() for a position, LIKE for basic patterns, and REGEXP for more expressive matching. Use a comparison operator when you want equality or ordering, not pattern matching. This makes a query easier to read and helps prevent unintended wildcard or conversion behavior.
Functions in a search condition can affect index use. For example, a normal index on username may be less useful for a condition that applies LOWER(username) to every row, depending on the query and schema. A prefix search such as username LIKE 'ada%' can often use a suitable B-tree index, whereas a leading wildcard such as username LIKE '%ada%' commonly cannot use that index for a direct range lookup. MySQL versions and index designs matter; functional indexes or generated columns may help for recurring transformed searches.
Also separate presentation formatting from stored data when possible. Adding spaces, punctuation, or zero-padding in a query may be appropriate for display, but storing those additions can complicate searching and updates. If a transformation is part of a comparison rule, apply it consistently and consider a schema or index design that supports the actual query pattern.
Common mistakes to watch for
- Using
+or assuming||always concatenates. PreferCONCAT()orCONCAT_WS(). - Using
LENGTH()when you mean a character count. Compare it withCHAR_LENGTH()for multibyte text. - Forgetting that
LIKEtreats percent and underscore as wildcards, or assuming it uses regular-expression syntax. - Expecting string comparisons to be case-sensitive without checking the column’s collation.
- Ignoring NULL propagation or assuming
TRIM()removes every type of whitespace. - Building SQL by joining user input into a query string. Use parameterized queries in application code; escaping alone is not a reliable substitute for parameter binding.
When debugging a surprising result, inspect the column type, character set, collation, SQL mode, and actual input bytes. A displayed string can conceal trailing spaces or characters that look similar but are encoded differently. Testing a small set of representative values, including NULL and non-ASCII text, often reveals whether the issue is in the function, comparison rules, or source data.
Frequently Asked Questions
How do I concatenate strings in MySQL?
Use CONCAT(value1, value2, ...), or CONCAT_WS(separator, value1, value2, ...) when you want a separator and NULL values after it skipped. Remember that CONCAT() returns NULL if any argument is NULL.
What is the difference between LENGTH() and CHAR_LENGTH()?LENGTH() counts bytes, while CHAR_LENGTH() counts characters. They can return different values for text encoded with a multibyte character set such as utf8mb4.
How can I check whether a string contains another string?
Use LOCATE(needle, haystack) > 0 to test whether a substring occurs. For simple wildcard searches, use LIKE; for more complex pattern rules, consider REGEXP.
Is MySQL string matching case-sensitive?
It depends on the comparison and the collation. Many common collations are case-insensitive, but others distinguish case. Check the collation on the relevant expression or column rather than assuming one universal rule.
Why does CONCAT() return NULL?
In MySQL, CONCAT() returns NULL if any argument is NULL. If you want NULL arguments omitted between values, use CONCAT_WS() with a non-NULL separator, while checking whether empty strings should be omitted too.
Related
- How to Group Rows in JMP
- How to Subtotal Data in Excel and Summarize Groups
- C++ Split String Into Array: Practical Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel 365 Table Formulas: Structured References ExplainedExcel 365 table formulas are formulas entered in an Excel table that can use readable column names, such…
- CLEAN in Excel: Formula, Examples, and Common LimitsIn Excel, use =CLEAN(A2) to remove nonprinting ASCII control characters from the text in cell A2. CLEAN can…
- Weekday Excel Shortcut: Formulas and Fast Ways to Get ItExcel has no dedicated keyboard shortcut that turns a selected date into its weekday. For a date in…
- Excel IFS vs COUNTIF: When to Use EachIn Excel, IFS tests conditions in order and returns a corresponding result, while COUNTIF counts cells that meet…
- String Functions in Java: GeeksforGeeks GuideString functions in Java, as covered on GeeksforGeeks, are built-in methods of the String class that let you…
- Cannot Filter in Excel? Causes, Checks, and FixesYes—Excel can filter both ordinary data ranges and tables. In Microsoft 365 for Windows, select a cell in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.