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 AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

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