SQL Server’s REPLACE(string_expression, string_pattern, string_replacement) function replaces every occurrence of one string with another and returns the revised string. For example, SELECT REPLACE('red,blue,red', 'red', 'green'); returns green,blue,green.
What the REPLACE function does
REPLACE searches a character expression for a specified pattern, then substitutes the replacement text wherever that pattern occurs. It is useful for straightforward text changes such as changing separators, removing unwanted characters, correcting known values, or standardizing a piece of text in a query.
The function replaces all matches, not just the first one. To remove a substring, use an empty replacement string. For example, SELECT REPLACE('AB-12-XY', '-', ''); returns AB12XY. If the pattern does not occur, the function returns the original expression unchanged.
REPLACE works on literal text, not on wildcard patterns. A pattern such as '%' means the percent character itself; it does not mean “any sequence of characters” as it does in a LIKE condition. That distinction is useful when cleaning data that contains punctuation or other special characters.
Syntax, arguments, and return type
The syntax is REPLACE(string_expression, string_pattern, string_replacement). Each argument supplies character data: the text to search, the exact substring to find, and the text to put in its place. The replacement can be longer, shorter, or empty. For example, SELECT REPLACE('2026/11/03', '/', '-'); returns 2026-11-03.
string_expressionis the source text to search.string_patternis the substring to locate.string_replacementis the text substituted for every match.
The return type is nvarchar if an input argument is nvarchar; otherwise, it is varchar. Use the N prefix on string literals when they contain Unicode text, as in N'café', and keep the column and literal types in mind when comparing or assigning results.
There is an important size limit: when string_expression is not varchar(max) or nvarchar(max), SQL Server truncates the result at 8,000 bytes. If a replacement could make a long result exceed that limit, cast the source expression to a large-value type. For example, with text stored in varchar, use REPLACE(CAST(Description AS varchar(max)), 'old', 'new'). The limit is measured in bytes, not characters, so the number of characters that fit depends on the data type and encoding.
Case sensitivity and collation
Whether a match is case-sensitive depends on the collation used for the searched expression. In a case-insensitive collation, searching for 'cat' may match 'Cat' as well; in a case-sensitive collation, those strings are different. Collation also affects other comparison details, including accent sensitivity.
If a query needs a particular comparison rule, apply a suitable collation to the source expression. For instance, REPLACE(ProductName COLLATE Latin1_General_100_CS_AS, N'a', N'x') uses a case-sensitive, accent-sensitive collation for that expression. Collation names available depend on the SQL Server installation, so select one supported by the target database. Changing a database or column’s collation is a broader decision than adjusting a single expression and can affect other comparisons.
This behavior can explain why a replacement appears to change more values than expected, or why it misses values that differ in capitalization. Before using the function in an update, check representative values that vary by case, accents, and data type.
Replacing several values or characters
A single call handles one pattern and one replacement. To apply several different substitutions, nest calls. For example, SELECT REPLACE(REPLACE(N'2026/11/03', N'/', N'-'), N'2026', N'FY26'); returns FY26-11-03. The inner call runs first, and the outer call runs on its result, so the order can affect the outcome. If one replacement inserts text that matches a later pattern, that inserted text may also be changed by the outer call.
For a few clear substitutions, nested calls are often easy to understand. For a long list of replacements, however, the expression can become hard to maintain. Consider whether the mapping belongs in a reference table or in application-level cleanup logic rather than building a deeply nested expression into every query.
If the goal is to map individual characters one-for-one, SQL Server 2017 and later also provide TRANSLATE. For example, SELECT TRANSLATE('1-2/3', '-/', '//'); returns 1/2/3. TRANSLATE maps characters by position and requires the two character lists to have the same length. Unlike nested REPLACE calls, it does not replace multi-character substrings or repeatedly process replacement text. Choose REPLACE for substring substitutions and TRANSLATE for simple character-by-character mappings.
Using REPLACE safely in an UPDATE
REPLACE can transform stored data when used in an UPDATE, but first preview the proposed changes. For example, to change every instance of old to new in a notes column, inspect affected rows with:
SELECT CustomerID, Notes, REPLACE(Notes, N'old', N'new') AS ProposedNotes FROM dbo.Customer WHERE CHARINDEX(N'old', Notes) > 0;
After checking the results and confirming the intended scope, the corresponding update is:
UPDATE dbo.Customer SET Notes = REPLACE(Notes, N'old', N'new') WHERE CHARINDEX(N'old', Notes) > 0;
The WHERE condition matters: without it, SQL Server processes every row, including rows that do not contain the pattern. A preview query helps catch unintended matches, such as a short pattern that appears inside a larger word. For a high-impact change, use an appropriate transaction and follow your organization’s backup and change-review procedures. Also verify that the destination column can hold the new value; assigning a longer result to a limited-width column can cause truncation or an error depending on the context.
Common problems and edge cases
- NULL arguments: If the source, pattern, or replacement is
NULL, the result isNULL. If a missing replacement should mean “leave the text unchanged,” handle that rule explicitly, for example withCOALESCE, rather than assuming REPLACE ignores nulls. - Empty pattern: An empty
string_patternleaves the source expression unchanged; it does not insert replacement text between every character. - No match: If the pattern is absent, the original text is returned. A query that produces unchanged values may simply be searching for the wrong spelling, capitalization under the active collation, or data type representation.
- Long output: Remember the 8,000-byte limit for a non-
maxsource expression. Cast the source tovarchar(max)ornvarchar(max)when a longer result is possible. - Unicode and encoding: Use Unicode literals and compatible column types for international text. Converting between
varcharandnvarcharcan affect how characters are represented. - Null character: The zero character,
char(0), cannot be included in a REPLACE pattern under Windows collations. If data may contain unusual control characters, investigate how it was imported and what SQL Server type and collation it uses.
Another common misunderstanding is to expect REPLACE to edit only the first match. It changes every match by design. SQL Server does not provide a first-match switch on this function; if only one occurrence should change, identify its position and use a positional technique such as STUFF with suitable logic.
REPLACE compared with other options
REPLACE is best when the target is a known substring and every occurrence should be substituted. Other functions suit different jobs:
STUFF: Deletes a specified number of characters at a position and inserts new text there. It is positional, so it is useful when the location of the change is known rather than when searching for every matching substring.TRANSLATE: Maps individual characters to corresponding characters in one pass. It is not a substitute for replacing a word or multi-character sequence.TRIM,LTRIM, andRTRIM: Remove spaces or specified characters from the ends of a string, rather than replacing matching text wherever it occurs.LIKEandCHARINDEX: Find or test for text; they do not return a transformed string. Use them in a filter when deciding which rows need an update.
For example, use REPLACE to turn every hyphen in a code into a slash. Use CHARINDEX to test whether a code contains a hyphen, and use STUFF when replacing text at a known character position. Choosing a function based on whether the task is substitution, searching, trimming, or positional editing makes the query easier to review.
Performance and data-quality considerations
REPLACE is a scalar string operation, so it is generally straightforward for short values and occasional transformations. Applying it to a large number of rows or very long strings still requires SQL Server to process those values. In a filter, wrapping an indexed column in a function, such as WHERE REPLACE(Code, '-', '') = 'AB12', can prevent a straightforward index seek on the unmodified column. For frequent searches, consider storing a normalized value, using an appropriate computed-column and index design, or changing the data model after evaluating the trade-offs.
For updates, the main costs can extend beyond calculating the replacement: changed rows may generate transaction log activity, affect indexes, and require locks. Limit the update with a precise predicate, estimate the number of affected rows, and consider batching large changes where that fits the application’s requirements. Do not use a broad replacement as a substitute for validating imported data; it may make different underlying problems look consistent without fixing their source.
Finally, think about whether the transformation should be permanent. If users need the original text, return a transformed expression in a SELECT rather than overwriting the stored value. If the rule is part of a durable data standard, document it and apply it consistently at the appropriate point in the data pipeline.
Frequently Asked Questions
Does SQL Server REPLACE change every occurrence?
Yes. It replaces every occurrence of the specified pattern in the source expression. It does not offer an option to stop after the first match.
How do I remove a character with REPLACE?
Use an empty replacement string, such as REPLACE('A-B-C', '-', ''). The result is ABC.
Is SQL Server REPLACE case-sensitive?
It depends on the collation used for the searched expression. A case-insensitive collation can match different capitalization; apply a case-sensitive collation to the expression if the operation must distinguish case.
Why is my REPLACE result truncated?
A result based on a source expression that is not a varchar(max) or nvarchar(max) is limited to 8,000 bytes. Cast the source expression to the appropriate max type when a longer result is needed.
How can I replace only the first match?
REPLACE always changes all matches. Use position-finding logic with CHARINDEX and a positional function such as STUFF when only a particular occurrence should be changed.
Related
- Snowflake String Contains: SQL Methods and Examples
- Print String and Int on Arduino
- Arduino String Data Type: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP to Find Duplicates in Two Worksheets in ExcelTo find values shared by two worksheets, enter a VLOOKUP formula beside the values on the first sheet…
- Excel Column Comparison: Formulas, Methods, and TipsTo compare two Excel columns, use a row-by-row equality test when corresponding cells should match, or check whether…
- Cash Flow Formula in Excel: A Step-by-Step TutorialIn Excel, net cash flow for a period is cash inflows minus cash outflows. If inflows are in…
- XLOOKUP Excel Criteria Match: Exact and Multiple CriteriaYes. XLOOKUP can match a criterion exactly by default, and it can also search with approximate or wildcard…
- COUNTIFS Formula for Date Range in Excel With ExamplesTo count dates within a range in Excel, use COUNTIFS with a start-date criterion and an end-date criterion.…
- Microsoft Excel Concatenate Text: Formulas and ExamplesTo concatenate text in Microsoft Excel, join cell contents with the ampersand operator (&), or use CONCAT or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.