The REPLACE function replaces part of a string with another string.
Comparisons in REPLACE are based on the database collation setting.
This example replaces 'kilograms' with 'kg'.
SELECT REPLACE('2 kilograms', 'kilograms', 'kg') AS Weight
| Weight |
|---|
| 2 kg |
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.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SElECT REPLACE(ProductName, 'Anton', 'Pierre') AS 'Product Name',
UnitPrice, Package
FROM Product
WHERE ProductName LIKE 'Chef Anton%'
| Product Name | UnitPrice | Package |
|---|---|---|
| Chef Pierre's Cajun Seasoning | 22.00 | 48 - 6 oz jars |
| Chef Pierre's Gumbo Mix | 21.35 | 36 boxes |
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName,
REPLACE(Package, 'oz', 'ounce') AS Package
FROM Product
WHERE Package LIKE '%oz%'
| 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 |
 |
|
