REPLACE() Function in SQL
Introduction
REPLACE is a string function that finds a specified character sequence inside text and substitutes another sequence. It can change words, separators, punctuation, codes, formatting marks, and other literal text in query output or stored values.
SELECT REPLACE('Hello World', 'World', 'SQL') AS changed_text;
-- Result: Hello SQL
The function normally replaces every non-overlapping match, not only the first. Replacing with an empty string removes matching text. REPLACE operates on character sequences rather than semantic words, so replacing cat in cat category can affect both occurrences.
Reliable production use requires more than knowing the syntax. Case and collation rules, NULL values, Unicode normalization, empty arguments, result length, large updates, indexing, and database differences all matter. Preview transformations before changing data and avoid using repeated text replacement as a permanent substitute for proper types and columns.
Basic REPLACE Syntax
The common form accepts the source expression, the text to find, and the replacement text.
REPLACE(string_expression, search_text, replacement_text)
The search is literal in the basic function. Wildcards such as percent and underscore do not act like LIKE patterns unless they are literal characters in the source.
SELECT REPLACE('Java Tutorial', 'Java', 'SQL') AS tutorial_name;
-- Result: SQL Tutorial
A Simple Replacement
The function scans the source for matching text and substitutes the requested replacement.
SELECT REPLACE('I like Java', 'Java', 'SQL') AS changed_text;
-- Result: I like SQL
If the search text is absent, the source is normally returned unchanged. REPLACE does not raise an error merely because no match exists.
Replacing Every Occurrence
Basic REPLACE generally changes all matching non-overlapping occurrences.
SELECT REPLACE('Java Java Java', 'Java', 'SQL') AS changed_text;
-- Result: SQL SQL SQL
A requirement to change only the first, last, or nth occurrence needs position functions, substring construction, regular expressions, or other DBMS-specific functionality.
Replacing a Single Character
A one-character search is useful for changing separators. Every slash in the following date-like text becomes a hyphen.
SELECT REPLACE('2026/09/26', '/', '-') AS changed_text;
-- Result: 2026-09-26
If the value represents a real date, store it using a DATE type and format it for presentation. Text replacement does not validate a calendar date.
Replacing Spaces with Another Separator
Ordinary spaces can be converted to hyphens for a simple machine-style label.
SELECT REPLACE('Hello SQL World', ' ', '-') AS changed_text;
-- Result: Hello-SQL-World
This does not collapse repeated spaces, remove tabs, or create a production-quality URL slug. It replaces exactly the specified ordinary space wherever it appears.
Removing Text with an Empty Replacement
Replacing a match with empty text removes it in databases that distinguish empty strings normally.
SELECT REPLACE('Hello World', ' ', '') AS compact_text;
-- Result: HelloWorld
Oracle permits omission of the replacement argument and treats empty character strings as NULL, so its behavior and syntax deserve separate migration tests.
Removing Hyphens
Hyphen removal is a common normalization example for controlled text such as a consistently formatted phone number.
SELECT REPLACE('847-555-1234', '-', '') AS compact_phone;
-- Result: 8475551234
Real phone normalization also needs country codes, parentheses, spaces, extensions, plus signs, and international numbering rules. Removing one character is not complete validation.
Removing Thousands Separators
Commas can be removed from formatted number text.
SELECT REPLACE('1,000,000', ',', '') AS numeric_text;
-- Result: 1000000
Amounts and quantities should generally use numeric data types. Formatting symbols belong at the presentation boundary, and locale-specific separators make text parsing fragile.
Using REPLACE with a Column
When the source is a column, each selected row receives a transformed output.
SELECT customer_id,
phone,
REPLACE(phone, '-', '') AS compact_phone
FROM customers;
This SELECT does not change the table. It is useful for previews, reports, and profiling before a cleanup rule is approved.
SELECT Does Not Modify Stored Data
A function in a SELECT expression changes only the returned value. The original column remains intact for later queries.
SELECT product_code,
REPLACE(product_code, '-', '') AS displayed_code
FROM products;
This distinction is essential during review. A successful preview proves the expression works on sampled rows, not that stored data has been normalized.
Permanently Replacing Stored Text
UPDATE stores the transformed value. A mass change requires a backup or recovery plan, impact review, transaction strategy, and post-update validation.
UPDATE customers
SET phone = REPLACE(phone, '-', '')
WHERE phone LIKE '%-%';
The WHERE clause limits writes to candidate rows, but it does not guarantee that every transformed value is valid or unique. Preview exactly the same condition first.
Replacing a Company Email Domain
A domain migration can replace a controlled suffix in employee addresses.
SELECT email,
REPLACE(email, '@oldcompany.com', '@newcompany.com') AS proposed_email
FROM employees
WHERE email LIKE '%@oldcompany.com';
Basic REPLACE changes every matching sequence, even if it appears somewhere other than a true domain suffix. A stricter migration should validate the address structure and confirm uniqueness before updating.
Changing File Extension Text
A simple replacement can turn report.txt into report.csv.
SELECT REPLACE(filename, '.txt', '.csv') AS proposed_filename
FROM files;
This also changes .txt wherever it occurs, including a directory or middle segment. Use suffix-aware logic when the requirement concerns only the final extension.
NULL Arguments
In common implementations, a NULL required argument makes the result NULL because the source, search, or replacement is unknown.
SELECT REPLACE(NULL, 'A', 'B') AS changed_text;
-- Typical result: NULL
Test each supported DBMS, especially Oracle's empty-string behavior. NULL should not be confused with a known empty source or a request to remove text.
Handling NULL with COALESCE
COALESCE can supply an explicit fallback before replacement when presentation logic intentionally treats missing text as empty.
SELECT REPLACE(COALESCE(description, ''), 'old', 'new') AS changed_description
FROM products;
This erases the distinction between missing and empty data in the result. Quality reports should usually preserve and count NULL separately.
Combining REPLACE with TRIM
Nested functions run from the inside outward. This expression first removes surrounding spaces and then removes hyphens.
SELECT REPLACE(TRIM(product_code), '-', '') AS compact_code
FROM products;
For ' ELEC-1001 ', the result is ELEC1001. The rule still leaves tabs or non-breaking spaces unless they are handled explicitly.
Combining REPLACE with UPPER and LOWER
Case conversion can produce a normalized display label after separator replacement.
SELECT UPPER(REPLACE(product_name, ' ', '-')) AS upper_label,
LOWER(REPLACE(employee_name, ' ', '.')) AS lower_label
FROM products;
Case mapping can be locale-sensitive, and a simple person-name-to-username transformation can create duplicates. Validate generated identifiers before storing them.
Combining REPLACE with CONCAT
REPLACE can normalize an existing component before CONCAT adds a prefix.
SELECT CONCAT('EMP-', REPLACE(employee_code, '-', '')) AS display_code
FROM employees;
Functions that produce visually similar text may express very different rules. Keep the transformation readable with clear aliases or staged expressions.
Nested REPLACE Calls
Nesting handles several known characters by passing the inner result to the outer call.
SELECT REPLACE(
REPLACE(
REPLACE(phone, '(', ''),
')', ''
),
'-', ''
) AS partially_normalized_phone
FROM customers;
Deep nesting quickly becomes difficult to read and easy to break. A TRANSLATE function, regular expression, staging pipeline, or application library may be more maintainable.
Order Matters in Nested Replacements
The inner function runs first. When replacement output can match a later search string, changing the order changes the final result.
SELECT REPLACE(REPLACE('AB', 'A', 'B'), 'B', 'C') AS result_one,
REPLACE(REPLACE('AB', 'B', 'C'), 'A', 'B') AS result_two;
Document the intended sequence and test intermediate values for multi-step transformations. Avoid chains whose correctness depends on an obscure ordering trick.
REPLACE Versus TRIM
TRIM targets supported characters at string boundaries. REPLACE changes matching sequences throughout the source.
SELECT TRIM(' John Smith ') AS boundary_cleaned,
REPLACE(' John Smith ', ' ', '') AS all_spaces_removed;
The first result is John Smith; the second is JohnSmith. Use TRIM when internal spaces should remain.
REPLACE Versus SUBSTRING
REPLACE modifies matching text, while SUBSTRING extracts a positional portion.
SELECT REPLACE('SQL Tutorial', 'SQL', 'Java') AS changed_text,
SUBSTRING('SQL Tutorial', 1, 3) AS extracted_text;
One operation produces Java Tutorial; the other produces SQL. Select the function according to the transformation, not the desired example output alone.
REPLACE Versus CONCAT
REPLACE changes content already present. CONCAT combines separate values. Both can produce Hello SQL, but their inputs and intent differ.
Clear intent matters for maintenance. A reviewer should be able to tell whether the query edits a source value or builds a new label from components.
REPLACE Versus TRANSLATE
REPLACE exchanges one substring for another. TRANSLATE, where supported, maps characters position by position and can handle several independent character substitutions in one call.
TRANSLATE is not a universal replacement for REPLACE. It has different length and mapping rules, and availability varies by product. Choose it only when the requirement is character mapping.
Creating Readable Labels
Underscores in machine-style identifiers can be replaced with spaces for a basic display label.
SELECT REPLACE('sql_string_functions', '_', ' ') AS display_label;
-- Result: sql string functions
This does not capitalize words, interpret acronyms, or localize the text. User-facing labels are often better stored in a resource or generated by presentation code.
Creating a Basic Slug
A simple combination can trim a title, replace spaces, and lowercase the result.
SELECT LOWER(REPLACE(TRIM(title), ' ', '-')) AS simple_slug
FROM articles;
Production slug creation also needs repeated whitespace handling, punctuation removal, Unicode transliteration policy, uniqueness, reserved words, length limits, and stable redirects when titles change.
Behavior When Search Text Is Missing
If no match exists, REPLACE commonly returns the original text unchanged.
SELECT REPLACE('Hello SQL', 'Java', 'Python') AS changed_text;
-- Result: Hello SQL
An unchanged result does not distinguish no match from replacing text with itself. Count or locate matches separately when an audit must prove whether a transformation occurred.
Empty Search Text
Behavior for an empty search argument varies and can be surprising. Some systems leave the source unchanged, while empty-string treatment in Oracle is tied to NULL semantics.
Avoid relying on REPLACE(value, '', 'x') for inserting text between characters. Use a documented, purpose-built method and verify the target version.
Case Sensitivity and Collation
Case matching can depend on the DBMS, collation, data type, and function semantics. Do not assume that searching for sql will match SQL on every platform.
If case-insensitive replacement is required, a regular-expression function with explicit flags or carefully designed application logic may be clearer. Lowercasing the entire source can destroy original capitalization.
REPLACE Does Not Understand Word Boundaries
The function matches character sequences. Replacing cat with dog in cat category can produce dog dogegory.
SELECT REPLACE('cat category', 'cat', 'dog') AS changed_text;
Standalone-word replacement needs tokenization, regular-expression boundaries, full-text processing, or application logic appropriate to the language. Even regex word boundaries have multilingual limitations.
Special Characters and Escaping
REPLACE can work with punctuation, quotes, slashes, and other characters supported by the data type. SQL string literal rules still apply.
SELECT REPLACE('Price: $100', '$', 'USD ') AS changed_text;
-- Result: Price: USD 100
Use parameters for user-supplied search and replacement values. Building SQL by concatenating untrusted text creates injection risk; REPLACE is not a security sanitizer.
Unicode and Normalization
Unicode text can have visually equivalent sequences with different underlying code points. A precomposed accented character may not match a base character followed by a combining mark.
Define a normalization policy before replacement when canonical equivalence matters. Test multilingual scripts, emoji sequences, and combining marks using the same column types and collations used in production.
Result Length and Truncation
A replacement can make text longer or shorter. The returned data type, maximum expression size, destination column width, and implicit conversions can determine whether the result succeeds, truncates, or raises an error.
Before an UPDATE, calculate maximum proposed lengths and verify the target column. Pay special attention to SQL Server expression types and large-object handling, Oracle empty strings, and multibyte encodings.
Using REPLACE in WHERE
A transformed predicate can match differently formatted values.
SELECT customer_id, phone
FROM customers
WHERE REPLACE(phone, '-', '') = '8475551234';
This can be useful during migration but may require transformation of many rows. It also handles only one formatting character and should not be presented as complete phone normalization.
REPLACE and Index Performance
Applying REPLACE to an indexed column can make a normal index less directly useful. The engine may scan values, calculate the expression, and compare each result.
For frequent searches, normalize at ingestion, store a separate searchable value, use a generated or computed column, or create a supported expression index. Confirm behavior with execution plans and representative data.
Generated Normalized Columns
A generated column can centralize a stable transformation and potentially support indexing or uniqueness.
-- Conceptual pattern; syntax varies by DBMS
ALTER TABLE customers
ADD normalized_phone AS (
REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', '')
);
Deep cleanup logic in a generated expression can become brittle. A dedicated ingestion pipeline may be better when formats are numerous or country-specific.
Sorting, Grouping, and Distinct Values
REPLACE can define a normalized representation for exploratory reports.
SELECT REPLACE(product_code, '-', '') AS compact_code,
COUNT(*) AS row_count
FROM products
GROUP BY REPLACE(product_code, '-', '')
ORDER BY compact_code;
Function-based grouping and sorting add computation. Persist a modeled normalized value when the representation becomes a frequent business key.
Finding Formatting Duplicates
Grouping by a transformed value can reveal candidates that differ only by selected formatting characters.
SELECT REPLACE(phone, '-', '') AS normalized_phone,
COUNT(*) AS occurrence_count
FROM customers
WHERE phone IS NOT NULL
GROUP BY REPLACE(phone, '-', '')
HAVING COUNT(*) > 1;
These are candidates, not confirmed duplicate people. More normalization and business identity checks may be required.
Counting a Character with Length Difference
Subtracting the length after removal can count single-character occurrences when both functions use compatible character semantics.
SELECT value,
CHAR_LENGTH(value)
- CHAR_LENGTH(REPLACE(value, '-', '')) AS hyphen_count
FROM text_samples;
Use the target DBMS's character-counting function. For a multicharacter search, divide by its character length only when matches are non-overlapping and semantics are clear.
Replacing Only the First Occurrence
Basic REPLACE changes every match. To change only the first, locate that match, preserve text before and after it, and concatenate the pieces with the replacement. Exact functions differ by DBMS.
A regular-expression replacement with an occurrence parameter may be simpler where supported. Test missing matches and Unicode positions carefully.
Replacing the Last or Nth Occurrence
The final or nth match requires additional position logic, reverse searching, regular expressions, or a split-and-rebuild operation. Complexity grows quickly when delimiters can be escaped or nested.
When the source is a structured path, filename, list, or document, prefer a parser that understands that structure instead of assembling fragile string expressions.
MySQL REPLACE
MySQL supports the common three-argument form and returns a transformed string. Matching is case-sensitive for this function in common usage even when some comparisons use case-insensitive collations.
SELECT REPLACE('Hello Java', 'Java', 'SQL') AS changed_text;
Test the exact character set and collation when replacement behavior is business-critical.
PostgreSQL REPLACE
PostgreSQL supports three-argument REPLACE for literal substring replacement and provides regexp_replace for pattern-based needs.
SELECT REPLACE('Hello Java', 'Java', 'SQL') AS changed_text;
Use regexp_replace only when a pattern is genuinely required. Literal REPLACE is easier to read and avoids regex escaping errors.
SQL Server REPLACE
SQL Server supports the common form, with matching influenced by input collation. Return-size behavior can depend on whether the expression is a large-value type.
SELECT REPLACE('Hello Java', 'Java', 'SQL') AS changed_text;
Cast deliberately when a replacement can exceed ordinary expression limits, and verify that the destination column is wide enough.
Oracle REPLACE
Oracle supports REPLACE and allows the replacement argument to be omitted, which removes the search string.
SELECT REPLACE('Hello Java', 'Java', 'SQL') AS changed_text
FROM dual;
Oracle treats empty character strings as NULL, so portability tests must cover empty arguments and results.
Cross-Database Comparison
| Database | Common support | Important note |
|---|---|---|
| MySQL | REPLACE(source, search, replacement) | Common function matching is case-sensitive |
| PostgreSQL | REPLACE and regexp_replace | Use regex only for pattern requirements |
| SQL Server | REPLACE | Collation and expression size matter |
| Oracle | REPLACE | Replacement can be omitted; empty string is NULL |
| SQLite | replace | Test type and collation expectations |
Safe UPDATE Workflow
Begin with a SELECT that shows the primary key, original value, and proposed value under exactly the intended filter. Count affected rows, check length expansion and uniqueness collisions, and review representative samples.
SELECT product_id,
description AS old_value,
REPLACE(description, 'old', 'new') AS proposed_value
FROM products
WHERE description LIKE '%old%';
Then use an appropriate transaction, backup, batch size, monitoring plan, and rollback procedure. Record the rule and final row count.
Collision Analysis Before Normalization
Different source values can become equal after replacement. Removing hyphens from AB-12 and A-B12 produces the same compact code.
SELECT REPLACE(product_code, '-', '') AS proposed_code,
COUNT(*) AS row_count
FROM products
GROUP BY REPLACE(product_code, '-', '')
HAVING COUNT(*) > 1;
Resolve collisions through business rules before updating a unique identifier. Never let a cleanup expression silently merge distinct entities.
Batching Large Replacements
A large update can hold locks, expand logs, delay replication, fire triggers, and invalidate caches. Process stable primary-key ranges in restartable batches when the operational environment requires it.
Monitor update counts, lock waits, log growth, replication lag, application latency, and errors. Batching syntax is DBMS-specific and should be reviewed by the team responsible for production operations.
Overlapping Matches
Basic REPLACE normally processes non-overlapping matches. That distinction matters when the search text can overlap with itself. For example, the sequence aaa contains two conceptual occurrences of aa if positions are considered independently, but those occurrences share a character.
Do not infer overlapping behavior from one small test and apply it universally. If overlapping replacement is truly required, define the expected position-advancement rule and use an algorithm or regular-expression feature that supports it explicitly. Include consecutive, overlapping, and boundary cases in tests.
Literal Replacement Versus Regular Expressions
REPLACE is best when the search text is literal. Regular-expression replacement is appropriate when matching depends on patterns such as repeated whitespace, optional punctuation, character classes, or word boundaries.
Regex functions differ significantly among MySQL, PostgreSQL, Oracle, SQL Server versions, and SQLite extensions. Pattern syntax, replacement backreferences, occurrence controls, flags, and Unicode behavior are not portable. Prefer literal REPLACE whenever it expresses the requirement, and isolate complex patterns behind well-tested database-specific code.
Preserving Original Capitalization
A case-insensitive search does not automatically imply a case-preserving replacement. Replacing sql, SQL, and Sql with one fixed value can flatten distinctions that the output should preserve.
If replacement text must follow the matched capitalization, basic REPLACE is insufficient. A regex engine with capture logic or application-level text processing may be needed. Document whether capitalization is data, presentation, or irrelevant normalization before choosing an implementation.
Replacing Inside JSON, XML, and HTML
Raw REPLACE does not understand structured documents. Changing a sequence inside JSON can affect a property name as well as a value. In XML or HTML it can alter element names, attributes, escaped text, scripts, or markup and produce an invalid document.
Use JSON, XML, or HTML-aware parsers and database functions when modifying structured content. Serialize the structure after changing the intended node. Literal replacement is acceptable only when the input contract proves that the sequence has one unambiguous role and the result is validated afterward.
Designing Idempotent Cleanup
An idempotent transformation produces the same result when run more than once. Removing hyphens is generally idempotent because a second run finds no hyphens. Replacing A with AA is not: every rerun expands the value again.
Production cleanup jobs should be restartable after partial failure. Add a filter, migration marker, batch record, or transformation version so rerunning does not corrupt previously processed rows. Test the expression twice against representative input and compare the first and second results.
Audit Logging and Sensitive Data
Recording old and new values can support reconciliation, but it can also expose email addresses, phone numbers, account references, or other personal data. Audit evidence should follow access-control, retention, masking, and encryption policies.
Store row identifiers, transformation versions, timestamps, and hashes where full values are unnecessary. Do not paste unrestricted before-and-after exports into tickets or shared logs. A cleanup process must improve data quality without creating a separate data-leak risk.
Replacement in an ETL Staging Layer
A staging table is a useful place to retain the raw source, calculate proposed normalized values, attach validation status, and quarantine failures before loading authoritative tables.
SELECT import_row_id,
raw_phone,
REPLACE(REPLACE(REPLACE(raw_phone, '-', ''), ' ', ''), '(', '') AS proposed_phone,
source_file_name
FROM customer_import_stage;
This approach makes rules observable and reversible. It also lets teams compare source batches, measure malformed formats, and evolve normalization without rewriting trusted production records repeatedly.
Using Views During a Migration
A view can expose a normalized representation while applications migrate away from dirty legacy data. Consumers can adopt the new column gradually without immediately rewriting the source.
CREATE VIEW customer_phone_view AS
SELECT customer_id,
phone AS stored_phone,
REPLACE(phone, '-', '') AS normalized_phone
FROM customers;
A view does not remove the runtime calculation or automatically solve index use. Treat it as a compatibility layer with an exit plan, not a permanent excuse to preserve invalid storage.
Post-Update Verification
After a permanent replacement, re-run candidate queries and collision checks, compare row and NULL counts, validate length ranges and constraints, and inspect representative records. Run regression tests for search, authentication, reports, exports, APIs, events, and downstream reconciliation.
Record the number of rows changed and unresolved exceptions. If new writes can still introduce the old format, update the ingestion contract before closing the migration. Otherwise, the cleanup will need to be repeated indefinitely.
Testing REPLACE Logic
Tests should include no match, one match, several matches, adjacent matches, overlapping-looking sequences, empty source, empty search, empty replacement, NULL arguments, boundary matches, internal-word matches, Unicode, long expansion, and destination-width limits.
Run the same cases on every supported database version. Verify output text, type, collation effects, execution plans, constraints, triggers, and downstream integrations.
Common Mistakes
Common errors include expecting only the first match to change, confusing REPLACE with TRIM, assuming SELECT modifies data, updating every row without a filter, and replacing a sequence inside larger words.
Other risks include using replacement to parse structured values, ignoring NULL and Unicode normalization, trusting an empty search string, overlooking result expansion, and repeatedly transforming indexed columns in filters.
Best-Practice Checklist
- Define the exact literal or pattern requirement before selecting a function.
- Remember that basic REPLACE normally changes every match.
- Use an empty replacement only when removal is intentional.
- Preserve NULL unless the business rule defines a fallback.
- Test case, collation, Unicode, and empty-argument behavior.
- Preview original and proposed values before any UPDATE.
- Check expansion, destination width, uniqueness, and constraints.
- Use transactions, backups, batching, and post-update validation.
- Normalize frequently searched values at ingestion or in modeled columns.
- Use parsers and proper data types for structured content.
Interview Questions and Answers
What does REPLACE do? It substitutes matching character sequences inside a source string with replacement text.
Does it replace every occurrence? Basic REPLACE generally replaces all non-overlapping matches.
How do you remove text? Replace it with an empty string, subject to the target DBMS's empty-string semantics.
What happens when no match exists? The original string is normally returned unchanged.
What happens with NULL? A NULL required argument normally produces NULL.
Is REPLACE the same as TRIM? No. REPLACE changes matches throughout a string, while TRIM targets boundaries.
Does SELECT REPLACE update a table? No. Permanent changes require UPDATE or another data-changing operation.
Can it replace only the first occurrence? Not directly with the basic function; use position logic, regex support, or DBMS-specific features.
Can REPLACE affect index use? Yes. Applying it to an indexed column in a predicate can make a normal index less useful.
Why preview before UPDATE? Replacement can affect unexpected substrings, expand values, cause collisions, violate constraints, and change many rows.
Key Takeaway
REPLACE transforms text by finding literal character sequences and substituting new content. It is effective for controlled normalization and presentation but does not understand words, file structures, dates, phone numbers, or business entities.
RELIABLE REPLACEMENT = PRECISE SEARCH RULE
+ EXPLICIT REPLACEMENT TEXT
+ NULL AND CASE POLICY
+ UNICODE AND LENGTH TESTING
+ SAFE UPDATE CONTROLS
+ VERIFIED QUERY PERFORMANCE
Use REPLACE for genuine text substitution, choose specialized functions for patterns or structured data, and treat permanent replacement as a reviewed data migration rather than a casual string operation.