How to use the OVERLAY() function in SQL

The SQL OVERLAY() function

The OVERLAY() function is used to replace a substring within a string at the given starting position.

Syntax

OVERLAY(string PLACING replacing_string FROM start [FOR count])

Parameters

  • string: It represents the string whose substring is to be replaced.
  • replacing_string: It represents the substring to be replaced with.
  • start: It represents the index to start the replacement.
  • count: This is an optional parameter. It represents the number of characters to be replaced.

Code example

The following code shows how to use the OVERLAY() function in SQL.

CREATE TABLE Product (
    id int,
    product_name varchar(50),
    price varchar(50),
    product_id varchar (20)
);
-- Insert data
INSERT INTO Product
VALUES (101,'T-shirt','$100','oo-01-345');
INSERT INTO Product
VALUES (102,'Hand Bag','$65','oo-01-238');
INSERT INTO Product
VALUES (103,'Ipad','$1200','oo-01-103');
INSERT INTO Product
VALUES (104,'Cereal','$30','oo-01-775');
INSERT INTO Product
VALUES (105,'Microwave','$520','oo-01-788');
INSERT INTO Product
VALUES (106,'cloth clips','$15','oo-01-924');
INSERT INTO Product
VALUES (108,'Zara Perfume','$120','oo-01-245');
-- Query 
SELECT product_id, product_name, OVERLAY(product_id PLACING 'bkEDA' FROM 1) AS ovl_product_id
FROM Product;

Code explanation

  • Lines 1–7: We create a table called Product, which has the columns id, product_name, price, and book_id.
  • Lines 11–21: We add data to the table.
  • Lines 24–26: We retrieve the data in the columns product_id and product_name. We then use the OVERLAY() function to replace the substring oo-01 of each product ID with bkEDA and store it in a new column ovl_product_id.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)