The REPLACE function

Use the REPLACE function to replace a certain set of characters in a character string with other characters.

In the following query, the REPLACE function replaces the unit column value each with item for every row that the query returns. The first argument of the REPLACE function is the expression to be evaluated. The second argument specifies the characters that you want to replace. The third argument specifies a new character string to replace the characters removed.

Figure 1. Query

SELECT stock_num, REPLACE(unit,"each", "item") cost_per, unit_price
   FROM stock
   WHERE manu_code = "HRO";

Figure 2. Query result

stock_num      cost_per   unit_price

1              case          $250.00
2              case          $126.00
4              case          $480.00
7              case          $600.00
110            case          $260.00
205            case          $312.00
301            item           $42.50
302            item            $4.50
304            box           $280.00
305            case           $48.00
309            case           $40.00
312            box            $72.00