How to use the REPEAT() function in SQL

Overview

The REPEAT() function is used to repeat a given string a certain number of times.

Syntax

REPEAT(string,repeating_number)

Parameters

  • string: This represents the string to be repeated.
  • repeating_number: This specifies the number of times the string will be repeated.

Code example

Let’s query the Writer table and repeat the character b in each book_id once.

The following code shows how to use the REPEAT() function in SQL:

CREATE TABLE Writer (
    id int,
    name varchar(50),
    gender varchar(10),
    state varchar(15),
    book_id varchar (20)
);
-- Insert data
INSERT INTO Writer
VALUES (101,'Sharon Peller','Female','Kogi','oo-01-345');
INSERT INTO Writer
VALUES (102,'Paul Dons','Male','Lagos','oo-01-238');
INSERT INTO Writer
VALUES (103,'Ameera Abedayo','Female','Imo','oo-01-103');
INSERT INTO Writer
VALUES (104,'Maria Elijah','Female','Lagos','oo-01-775');
INSERT INTO Writer
VALUES (105,'David Hassan','Male','Abuja','oo-01-788');
INSERT INTO Writer
VALUES (106,'Mara Disu','Female','Lagos','oo-01-924');
INSERT INTO Writer
VALUES (108,'Joe Smith','Male','Lagos','oo-01-245');
-- Query 
SELECT id,CONCAT(REPEAT('b',1),book_id) AS new_bkid, name
FROM Writer;

Code explanation

In the code above:

  • Lines 1 to 7: We create a table called Writer which has the columns id, name, gender, state, and book_id.
  • Lines 11 to 21: We add data into the Writer table.
  • Lines 24 to 26: We repeat the character b to each data point in the book_id using REPEAT() and then concatenate it with each book’s ID using the CONCAT() function. Finally, we display the result.

Free Resources

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