String functions in SQL are built-in operations that let you manipulate, format, and extract text data stored in character columns. The most common ones include CONCAT, SUBSTRING, LENGTH, UPPER, LOWER, TRIM, and REPLACE, but the exact syntax and names vary by database system (MySQL, SQL Server, PostgreSQL, Oracle). This guide explains how they work, shows concrete examples, and covers the differences you’ll encounter across major SQL dialects.
What Are String Functions and Why Use Them?
String functions process text values within a SQL query. They are essential for cleaning messy data, formatting output, searching for patterns, and transforming stored information without changing the underlying table. For example, you might need to combine first and last name columns, extract the domain from an email address, or remove extra spaces before comparing values.
Every major SQL database includes a library of string functions, but the names and behaviors are not standardized. A function that works in MySQL might not exist in SQL Server, or it might have a different name. Understanding the core operations helps you write portable queries and debug errors when switching database platforms.
Core String Functions by Category
Here are the most frequently used string functions, grouped by what they do. The examples assume a table named users with a column email containing values like 'john.doe@example.com'.
- Concatenation: Combine strings. MySQL and PostgreSQL use
CONCAT(); SQL Server uses+orCONCAT()(since 2012). Example:SELECT CONCAT(first_name, ' ', last_name) FROM users; - Substring extraction: Pull part of a string.
SUBSTRING(str, start, length)works in MySQL, PostgreSQL, and SQL Server. Example:SELECT SUBSTRING(email, 1, 5) FROM users;returns'john.'. - Case conversion:
UPPER()andLOWER()change text to uppercase or lowercase. Available in all major systems. - Trimming: Remove spaces or characters from the start/end.
TRIM()is standard; MySQL also hasLTRIM()andRTRIM(). SQL Server hasLTRIM()andRTRIM()butTRIM()only since 2017. - Replacement: Replace occurrences of a substring.
REPLACE(str, from, to)works everywhere. Example:SELECT REPLACE(email, 'example.com', 'company.org') FROM users; - Length:
LENGTH()in MySQL/PostgreSQL,LEN()in SQL Server. Returns character count (or byte count in some cases). - Position: Find the index of a substring.
POSITION('@' IN email)in PostgreSQL,LOCATE('@', email)in MySQL,CHARINDEX('@', email)in SQL Server. - Reverse:
REVERSE()reverses a string. Available in MySQL, PostgreSQL, SQL Server, Oracle.
Practical Examples: Extracting and Cleaning Text
Suppose you have a users table with columns full_name and email. You want to extract the first name and the email domain. Here’s how you’d do it in MySQL:
-- First name (everything before first space)
SELECT SUBSTRING_INDEX(full_name, ' ', 1) AS first_name FROM users;
-- Email domain (everything after '@')
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;
In SQL Server, the same logic uses CHARINDEX and SUBSTRING:
-- First name
SELECT LEFT(full_name, CHARINDEX(' ', full_name) - 1) AS first_name FROM users;
-- Email domain
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain FROM users;
Notice how SUBSTRING_INDEX is MySQL-specific; SQL Server uses LEFT and CHARINDEX. These differences are common and worth memorizing if you work with multiple databases.
Common Problems and How to Avoid Them
String functions can produce unexpected results if you ignore data types, NULLs, or collation settings. Here are the most frequent pitfalls:
- NULL propagation: In most databases,
CONCAT(NULL, 'abc')returnsNULL. UseCOALESCE()orCONCAT_WS()(MySQL) to handle NULLs. Example:SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) FROM users; - Off-by-one errors: String positions start at 1 in SQL, not 0.
SUBSTRING('abc', 2, 1)returns'b'. - Length vs. bytes:
LENGTH()in MySQL returns bytes;CHAR_LENGTH()returns characters. For UTF-8 text with accents, useCHAR_LENGTH(). - Collation sensitivity: Case-insensitive collations (e.g.,
utf8_general_ci) makeUPPER()andLOWER()behave as expected, but case-sensitive collations can causeREPLACE()to miss matches. - Trailing spaces:
LENGTH()in SQL Server ignores trailing spaces, butDATALENGTH()includes them. This can cause confusion when comparing lengths.
SQL Dialect Differences: MySQL vs. SQL Server vs. PostgreSQL
The same logical operation often has different function names across databases. Here’s a quick comparison table for common tasks:
- Concatenation: MySQL/PostgreSQL:
CONCAT(); SQL Server:+orCONCAT(). - Substring: All use
SUBSTRING(str, start, length), but MySQL also hasSUBSTR()as an alias. - Find position: MySQL:
LOCATE()orINSTR(); SQL Server:CHARINDEX(); PostgreSQL:POSITION()orSTRPOS(). - Trim spaces: MySQL/PostgreSQL:
TRIM(); SQL Server:LTRIM()/RTRIM()orTRIM()(2017+). - Length: MySQL/PostgreSQL:
LENGTH(); SQL Server:LEN(). - Split string: MySQL:
SUBSTRING_INDEX(); PostgreSQL:SPLIT_PART(); SQL Server:STRING_SPLIT()(2016+) orPARSENAME()for simple cases.
If you’re writing queries that must run on multiple databases, stick to standard functions like UPPER, LOWER, SUBSTRING, and REPLACE, and avoid dialect-specific ones unless you control the environment.
Performance and Best Practices
Using string functions in WHERE clauses can prevent index usage, leading to full table scans. For example, WHERE UPPER(name) = 'JOHN' is non-sargable. Instead, store data in a consistent case or use a computed column. Here are some tips:
- Avoid wrapping indexed columns in functions in
WHEREclauses. UseWHERE name = 'John'if case is consistent. - Use
LIKEwith wildcards carefully; leading wildcards ('%abc') also kill index usage. - When extracting substrings repeatedly, consider adding a generated column (e.g.,
email_domain) to the table. - For large datasets, test the execution plan to see if string functions cause scans.
- Use
CAST()orCONVERT()when combining strings with numbers to avoid implicit conversion errors.
Frequently Asked Questions
What is the difference between CHARINDEX and SUBSTRING in SQL Server?
CHARINDEX returns the starting position of a substring within a string (e.g., CHARINDEX('@', 'a@b') returns 2). SUBSTRING extracts a portion of a string given a start position and length. They are often used together: first find the position with CHARINDEX, then use that number inside SUBSTRING to extract text after a delimiter.
How do I concatenate strings with NULL values in SQL?
In most databases, concatenation with a NULL yields NULL. Use COALESCE to replace NULLs with an empty string. In MySQL, CONCAT_WS() (concatenate with separator) skips NULLs automatically. In SQL Server, CONCAT() implicitly converts NULLs to empty strings, but the + operator does not.
Does SQL have a function to split a string into rows?
Yes, but it varies. SQL Server has STRING_SPLIT() (2016+). PostgreSQL has STRING_TO_TABLE() or UNNEST(STRING_TO_ARRAY()). MySQL does not have a built-in split function; you often use recursive CTEs or a numbers table. Oracle has REGEXP_SUBSTR with connect by.
Why does LENGTH() return a different number than I expect for accented characters?
In MySQL, LENGTH() returns the number of bytes, not characters. For UTF-8, accented characters take 2 bytes, so LENGTH('é') returns 2. Use CHAR_LENGTH() to get the character count. In SQL Server, LEN() returns character count, but DATALENGTH() returns bytes.
Can I use string functions in the WHERE clause?
Yes, but be aware of performance implications. Using a function on a column in a WHERE clause often prevents index usage. For example, WHERE SUBSTRING(name, 1, 1) = 'A' won’t use an index on name. If you need this frequently, consider a computed column or a different query design.
What is the best way to trim all spaces from a string?
To remove leading and trailing spaces, use TRIM() (or LTRIM()/RTRIM()). To remove all spaces (including internal), use REPLACE(str, ' ', ''). For example, SELECT REPLACE(' a b ', ' ', '') returns 'ab'.
Related
- String to Int in C++: Safe Conversion Methods
- Split in Oracle SQL: Turn Delimited Text Into Rows
- Python join() Explained: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- BMI Formula in Excel: Calculate It Step by StepTo calculate BMI in Excel, divide weight in kilograms by height in metres squared; for example, with weight…
- Excel Rounding: Does It Always Round Up in Formulas?No. Excel does not always round up: it can round to the nearest value, round away from zero,…
- Dense Rank in Excel: Formulas and ExamplesTo calculate a dense rank in Excel, give equal values the same rank and do not leave gaps…
- Compare Formula in Excel: Check If Values Match or DifferTo compare two cells in Excel, use an equality test: with the values to compare in A2 and…
- Excel SUMIF Does Not Contain Text: Formula GuideThe direct answer: Excel’s SUMIF function cannot directly sum cells that do not contain specific text because its…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.