Back to list SQL Server How to Replace Part of a String in T-SQL Database: SQL Server Operators:REPLACE, COLLATE Problem: You’d like to replace part of a string with another string in T-SQL. Example: Our database has a table named life_insurance with data in the following columns: policy_ID, last_name, and first_name. policy_IDlast_namefirst_name v-01Anstruther - GoughGary V-23Elliot - Murray - StewartMary 3A-vSmith - DorrieAlex As you can see, some policyholders have hyphenated surnames. We’d like to change the separator between the parts of these policyholders’ last names. Currently, the separator consists of a space, a hyphen ( - ), and another space; we want to replace this with a single hyphen (-). Solution 1: SELECT REPLACE( last_name, ' - ', '-' ) AS correct_last_name, first_name FROM life_insurance; This query returns a list of policyholders with correctly hyphenated last names, i.e. names separated by a hyphen instead of space-hyphen-space: correct_last_namefirst_name Anstruther-GoughGary Elliot-Murray-StewartMary Smith-DorrieAlex Discussion: Use the T-SQL function REPLACE() to replace a substring (a word, character, group of letters, etc.) with another substring. The target can be a string, an expression (or an expression returning a string) or a column name. This function takes three arguments: The target string, expression, etc. (In our case, it’s the column last_name.) The substring to replace (here, the space-hyphen-space pattern ‘ - ’). The substring to insert (here, a hyphen ‘-’). Notice that this function replaces all occurrences of the substring in a given text or column, not just the first occurrence. In our example, Mary’s complex surname consists of three names, so her last_name contains two occurrences of ‘ - ’, which were replaced by ‘-’. In the next example, we replace the character ‘v’ with ‘5’ in the policy ID numbers. Solution 2: SELECT REPLACE( policy_id, 'v', '5' ) AS new_policy_id, last_name, first_name FROM life_insurance; Here is the result: policy_idlast_namefirst_name 5-01Anstruther - GoughGary 5-23Elliot - Murray - StewartMary 3A-5Smith - DorrieAlex This query replaces both ‘v’ and ‘V’ with ‘5’ in the policy_id column. By default, SQL Server’s REPLACE function is not case sensitive, but this actually depends on your database server settings. You can specify whether this function is case sensitive or insensitive by using the COLLATE clause. Look at the last example: Solution 3: SELECT REPLACE( policy_id COLLATE SQL_Latin1_General_CP1_CS_AS, 'v', '5' ) AS new_policy_id, last_name, first_name FROM life_insurance; Here is the result: policy_idlast_namefirst_name 5-01Anstruther - GoughGary V-23Elliot - Murray - StewartMary 3A-5Smith - DorrieAlex In this query, the ‘V’ was not replaced because the REPLACE function is now case sensitive (i.e. ‘v’ is not the same as ‘V’). In the COLLATE clause, _CS denotes case-sensitivity; if you change it to _CI, the function will be case insensitive. What’s a collation? In SQL Server, it’s a set of rules that determine how values will be compared and sorted and how accent marks will be treated. It can be handled differently in different database systems. COLLATION is the SQL clause that sets the collation by using various options like _CI, _CS, and _AS and the name of an encoding set (like SQL_Latin1_General). You can read more about COLLATE in the SQL Server documentation. Recommended courses: SQL Basics SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Replace Part of a String in T-SQL How to Find the Number of Days Between Two Dates in MySQL How to Find the Last Day of the Month for a Given Date in MySQL How to Change Seconds to a Time Value in MySQL How to Change Datetime Formats in MySQL How to Get the Date from a Datetime Column in MySQL How to Get the Current Date and Time in MySQL How to Add Days to a Date in MySQL How to Add Time to a Datetime Value in MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.