SQL -Find and replace text in string

The following T-SQL for Microsoft  SQL Server shows how to find and replace text in a string.

In this example we’ll change “PO Box” to “P.O. Box”.

The REPLACE  function will search for a string and replace only the matched text – but I like to also use the WHERE condition to limit the number of changes being applied to the database.

Note: this is case insensitive – it will match again ‘po box’ as well.

UPDATE T_TableName
 SET Address_Line_1 = REPLACE( Address_Line_1, 'PO Box', 'P.O. Box' )
 WHERE ( Address_Line_1 LIKE '%PO Box%' )
Tags:

Was this article helpful?

Related Articles