SQL Server REPLACE Function: Syntax and Examples

Formulas & functions · Updated November 22, 2026

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_expression is the source text to search.
  • string_pattern is the substring to locate.
  • string_replacement is 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 is NULL. If a missing replacement should mean “leave the text unchanged,” handle that rule explicitly, for example with COALESCE, rather than assuming REPLACE ignores nulls.
  • Empty pattern: An empty string_pattern leaves 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-max source expression. Cast the source to varchar(max) or nvarchar(max) when a longer result is possible.
  • Unicode and encoding: Use Unicode literals and compatible column types for international text. Converting between varchar and nvarchar can 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, and RTRIM: Remove spaces or specified characters from the ends of a string, rather than replacing matching text wherever it occurs.
  • LIKE and CHARINDEX: 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.