SQL string functions transform, inspect, search, and format text; string operators combine text or compare it with patterns. Common tools include UPPER, LOWER, TRIM, SUBSTRING, REPLACE, LIKE, and a concatenation operator such as || or +. Exact names and behavior vary by database, so check your system’s syntax—especially for concatenation, nulls, and string length.
What SQL strings are and why dialect matters
A string is a sequence of characters stored in a text column or written as a quoted literal. SQL commonly uses single quotes for text, as in 'North'; to include a single quote inside a literal, many databases escape it by doubling it: 'O''Brien'. Double quotes usually identify column or table names, though database settings can affect that convention.
SQL has a standard, but database products implement string features with differences in function names, argument order, character encoding, and null handling. PostgreSQL, MySQL, SQL Server, Oracle, and SQLite do not provide an identical string toolbox. A query written for one system may need small changes—or produce a different result—on another. The examples below use common syntax and identify notable variations rather than implying every example works unchanged everywhere.
Combining strings
Concatenation joins text values. The SQL-standard operator is ||, used by PostgreSQL, Oracle, and SQLite. SQL Server commonly uses +, while MySQL commonly uses CONCAT(). PostgreSQL and MySQL also provide CONCAT(). MySQL can interpret || as logical OR unless a relevant SQL mode enables its concatenation meaning, so it is not a portable choice there.
For example, in PostgreSQL, SELECT first_name || ' ' || last_name AS full_name FROM customers; joins a first name, a space, and a last name. In SQL Server, the equivalent can use +, or CONCAT(first_name, ' ', last_name). In MySQL, use CONCAT(first_name, ' ', last_name). Choose the version appropriate to the database you actually run.
Null behavior is important. In MySQL, CONCAT() returns null if any argument is null. PostgreSQL’s CONCAT() treats null arguments as empty strings. SQL Server’s CONCAT() also treats null arguments as empty strings. Operators can differ too: concatenating a null with || generally produces null, and SQL Server’s + follows its null-concatenation behavior. If a missing value should count as blank, handle it deliberately with a function such as COALESCE(column, ''), after confirming the intended result.
Changing case, trimming, and replacing text
UPPER(text) converts letters to uppercase, and LOWER(text) converts them to lowercase. These functions are useful for display normalization or simple case-insensitive comparisons, but case conversion is governed by character set and collation rules. It may not perform every language-specific transformation a human expects. Applying a function to a column in a filter can also prevent an ordinary index from being used, depending on the database and available indexes.
Whitespace cleanup is another common task. TRIM(text) removes leading and trailing spaces in many systems; LTRIM and RTRIM remove them from one side. Support for choosing which characters to remove, and the syntax for doing so, differs. Trimming is not the same as collapsing repeated spaces inside a value: ' Ada Lovelace ' becomes 'Ada Lovelace', with the two internal spaces still present.
REPLACE(text, search_text, replacement_text) substitutes matching text within a value. For example, REPLACE(phone, '-', '') removes hyphens from a phone-number string in systems that use this common argument order. It does not validate whether the result is a valid phone number, and replacement behavior may depend on collation and case sensitivity. Use it for a defined transformation, not as a substitute for input validation.
Extracting parts and measuring strings
SUBSTRING returns part of a string. In many databases, a familiar form is SUBSTRING(text, start, length); SQL Server uses that form, while PostgreSQL also accepts a SQL-style form such as SUBSTRING(text FROM start FOR length). Position numbering and treatment of out-of-range positions should be checked for the target database. For example, extracting a fixed number of characters is not necessarily the same as extracting a word, because names and addresses may have varying lengths.
Related functions include LEFT(text, count) and RIGHT(text, count) in systems that support them. They return characters from the beginning or end. To locate text, common options include POSITION, CHARINDEX, INSTR, or STRPOS; the available function and argument order depend on the database. These functions often return a position or zero when no match is found, but confirm the specific system’s convention before using the result in calculations.
Length functions deserve special attention. LENGTH, CHAR_LENGTH, or LEN may count characters, bytes, or characters under rules specific to the product. In SQL Server, LEN excludes trailing spaces, whereas DATALENGTH reports the number of bytes used. A Unicode character can require multiple bytes, and some visible symbols consist of multiple code points. Therefore, a reported length may not match the number of bytes, displayed symbols, or user-perceived characters.
Searching with LIKE and related patterns
The LIKE operator checks whether a string matches a pattern. The percent sign (%) represents zero or more characters, and underscore (_) represents one character in the common SQL pattern syntax. For example, WHERE email LIKE '%@example.com' finds values ending in that text. WHERE code LIKE 'A_7' matches an A, any one character, and then 7.
A wildcard can be searched as a literal character by using an escape character with ESCAPE; exact syntax and default escaping rules should be checked for the database. Case sensitivity is often controlled by collation rather than by LIKE alone. A leading wildcard, as in '%term%', can make a conventional index less useful because the search does not begin at a known prefix. For advanced full-text searching, database-specific full-text features may be a better fit.
Choosing the right expression and avoiding common errors
Before using a string expression, decide whether you are changing data for display, comparing values, or permanently cleaning stored data. Those are different jobs. A query can format an output without changing the underlying column; an update changes stored information and should be tested carefully. For important data, keep the original value or use a controlled migration so the transformation can be reviewed.
- Check nulls: A null is not the same thing as an empty string. Decide whether missing text should remain unknown or be treated as blank.
- Check character rules: Collation, Unicode handling, and trailing spaces can affect matching and length.
- Check indexes: Wrapping an indexed column in a function may change whether a query can use its index efficiently. Some databases support expression or computed-column indexes.
- Check parameterization: Do not build SQL by joining user input into a query string. Use parameterized queries to keep input data separate from SQL code.
- Check portability: Confirm function names and argument order against the database documentation before moving a query between products.
One frequent misconception is that trimming or changing case makes two values universally equivalent. It does not. For instance, lowercasing may help with a simple comparison, but it does not settle every accent, locale, Unicode, or collation question. When a comparison rule matters to an application, define that rule explicitly and use the database’s supported collation or normalization approach.
Performance, safety, and data quality
String functions are usually straightforward, but applying them row by row across a very large table can add work. A predicate such as WHERE LOWER(username) = 'sam' may not use a regular index on username in the same way as a direct equality test. Options include storing a normalized value when appropriate, using a case-insensitive collation, or creating a supported functional or computed-column index. The best option depends on the database and workload, so examine the query plan rather than assuming a particular expression is fast.
String cleanup should also respect meaning. Removing punctuation from an identifier, address, or product code can merge values that are distinct in the source system. Similarly, truncating text can discard information, and replacing characters can change a value’s interpretation. Define the desired transformation, test it on representative edge cases, and retain a way to recover original data when the operation is destructive.
For security, escaping and concatenation are not substitutes for parameterized SQL. A string function can manipulate text, but it does not make arbitrary input safe to insert into a query. Bind values using the parameter mechanism provided by the application’s database driver. If a user must select a column or sort direction, validate against an explicit allowlist because those SQL elements generally cannot be supplied as ordinary value parameters.
Frequently Asked Questions
What is the SQL operator for joining strings?
The standard concatenation operator is ||, but the correct choice varies. SQL Server commonly uses +, and MySQL commonly uses CONCAT(). Check the database dialect and its null behavior.
How do I remove spaces from a SQL string?
Use TRIM() to remove leading and trailing whitespace where supported, or use LTRIM() and RTRIM() for one side. To remove or replace spaces throughout a value, use a suitable replacement expression; trimming alone does not alter internal spaces.
How can I get part of a string in SQL?
Use SUBSTRING with a starting position and, commonly, a length. The argument form and edge-case behavior vary by database. LEFT and RIGHT can be convenient when you need a fixed number of characters from one end.
Does SQL LIKE distinguish uppercase and lowercase?
It depends on the database’s collation and configuration. Some comparisons are case-insensitive by default and others are case-sensitive. Check the column or expression’s collation instead of assuming one universal rule.
What is the difference between NULL and an empty string?NULL represents a missing or unknown value, while '' is a present string containing zero characters. They compare and concatenate differently in many SQL expressions, so choose intentionally whether a missing value should remain null or be displayed as blank.
Related
- Split by Position in Power Query: A Practical Guide
- TinyTask for Sol’s RNG: What It Can and Can’t Do Safely
- Java split() Method: Split Strings with Regex and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
- Excel INDEX MATCH Tutorial: Exact and Flexible LookupsTo look up a value with INDEX MATCH in Excel, use MATCH to find the position of a…
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
- VLOOKUP Between Two Excel Sheets: Step-by-StepTo use VLOOKUP between two sheets in the same Excel workbook, enter a formula on the sheet where…
- SQL Server String Functions: A Practical GuideSQL Server string functions let you inspect, search, extract, combine, replace, and format text in queries. The most…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.