Logo
Back

How to UPDATE and REPLACE part of a string?

In SQL Server, you can easily replace part of a string value stored in a column by using built-in string functions such as REPLACE and STUFF. Below are two popular approaches—using UPDATE with REPLACE for complete substring replacements, and using STUFF for more precise substring manipulation.

1. Using REPLACE for Substring Replacements

REPLACE finds occurrences of a specified substring and replaces them with a new substring. This is especially useful when you want to change all instances of a particular text in a column.

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, 'OldText', 'NewText')
WHERE YourColumn LIKE '%OldText%';  -- Optional filter
  • YourTable: The table name.
  • YourColumn: The column you want to update.
  • OldText: The exact text to search for within YourColumn.
  • NewText: The text to replace the old substring with.
  • WHERE: Use a WHERE clause to limit updates to rows that actually contain OldText (optional but usually recommended).

Example

If you have a column storing “NY, USA” as part of an address, and you want to change “NY” to “New York”:

UPDATE Addresses
SET CityState = REPLACE(CityState, 'NY,', 'New York,')
WHERE CityState LIKE '%NY,%';

All instances of “NY,” will be replaced with “New York,” in CityState.

2. Using STUFF for Precise Character Replacement

STUFF can be used to insert or remove a substring by specifying a start position and length. It is helpful when you need to replace a specific portion of the string at a fixed location rather than all occurrences of a substring.

UPDATE YourTable
SET YourColumn = STUFF(YourColumn, start_position, length_to_replace, 'NewText')
WHERE <condition>;
  • start_position: The character position at which the replacement begins.
  • length_to_replace: The number of characters to remove from the original string.
  • NewText: The new string to be inserted.

Example

Suppose you have phone numbers in the format (123)456-7890 but you need them to be in 123-456-7890. Here’s how you could remove the parentheses:

UPDATE Contacts
SET PhoneNumber = STUFF(PhoneNumber, 1, 1, '')   -- Remove the first '('
WHERE PhoneNumber LIKE '(%';

-- Then remove the other ')'
UPDATE Contacts
SET PhoneNumber = STUFF(PhoneNumber, 4, 1, '')   -- Adjust positions if needed
WHERE PhoneNumber LIKE '%)';

Each STUFF call pinpoints an exact character to remove, preserving the rest of the string.

3. Best Practices

  1. Test Queries on a Subset: Use a SELECT query or a test environment to ensure your new strings look correct before updating real data.
  2. Backup or Transaction: For large or critical data, consider wrapping your update in a transaction or backing up the table to prevent accidental data corruption.
  3. Use WHERE Clause Wisely: Narrow down which rows need updating. Without a WHERE clause, you’ll change every row, which may cause unintended consequences.
  4. Check Collation and Case Sensitivity: If your database or column has case-sensitive collation, the REPLACE function will only match case-sensitive occurrences.

4. Strengthen Your SQL Mastery with DesignGurus.io

  1. Grokking SQL for Tech Interviews – Dive into essential query writing, optimization techniques, and hands-on practice questions commonly asked in technical interviews.
  2. Grokking Database Fundamentals for Tech Interviews – Explore best practices for designing efficient schemas, indexing strategies, and transaction handling.

For more advanced topics and practical tips, check out the DesignGurus.io YouTube channel. If you’re actively interviewing or just want personalized feedback, consider their Mock Interviews with ex-FAANG engineers for real-world insights.

Conclusion

To update and replace part of a string in SQL Server, use UPDATE in conjunction with string functions tailored to your specific needs:

  • REPLACE is best for finding and replacing a given substring throughout the entire string.
  • STUFF is ideal for precise insertions or deletions at specific character positions.

Always test your approach on a sample dataset or within a transaction to ensure you achieve the correct results without compromising data integrity.

CONTRIBUTOR
TechGrind