SQL REPLACE Function

The REPLACE function replaces part of a string with another string.

Comparisons in REPLACE are based on the database collation setting.

Example

#

This example replaces 'kilograms' with 'kg'.

SELECT REPLACE('2 kilograms', 'kilograms', 'kg') AS Weight
Result:  1 record
Weight
2 kg

Syntax

Syntax for the REPLACE function.

REPLACE (string, pattern, replacement)

string -- the input expression or column name to be changed.

pattern -- the string to search for within the expression.

replacement -- the string that replaces the searched string.


More Examples

REPLACE. Column value.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List Chef Anton's products as Chef Pierre's products.
SElECT REPLACE(ProductName, 'Anton', 'Pierre') AS 'Product Name', 
       UnitPrice, Package 
  FROM Product
 WHERE ProductName LIKE 'Chef Anton%'
Result:  2 records
Product Name UnitPrice Package
Chef Pierre's Cajun Seasoning 22.00 48 - 6 oz jars
Chef Pierre's Gumbo Mix 21.35 36 boxes

REPLACE. Column value.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products with 'oz' packaging replaced with 'ounce'.
SELECT ProductName, 
       REPLACE(Package, 'oz', 'ounce') AS Package
  FROM Product 
 WHERE Package LIKE '%oz%'
Result:  12 records
ProductName Package
Chang 24 - 12 ounce bottles
Chef Anton's Cajun Seasoning 48 - 6 ounce jars
Grandma's Boysenberry Spread 12 - 8 ounce jars
Northwoods Cranberry Sauce 12 - 12 ounce jars
Sasquatch Ale 24 - 12 ounce bottles

You may also like



Guides


vsn 3.1