How to use the BTRIM() function in SQL

Overview

The BTRIM() function is used to trim all the specified characters in the string_trim argument from the beginning and the end of the given string.

Syntax

STRPOS(string,string_trim)

Note: If the string_trim argument is not specified, the leading and trailing space is removed.

Parameter

  • string: This represents the string to be searched.
  • string_trim: This represents the characters to be removed.

Example

Let’s assume we have a dataset called Writer, and it contains information about several writers’ names, genders, and the states in which they were born. However, when we fetch the data from the database, we notice that a string back and kacb has been added to the writers’ names. How do we solve this problem?

Well, we can solve this problem using the BTRIM function.

The following code shows how we can do this:

CREATE TABLE Writer (
    id int,
    name varchar(50),
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Writer
VALUES (01,'backSharon Pellerkacb','Female','Kogi');
INSERT INTO Writer
VALUES (02,'backPaul Donskacb','Male','Lagos');
INSERT INTO Writer
VALUES (03,'backAmeera Abedayokacb','Female','Imo');
INSERT INTO Writer
VALUES (04,'backMaria Elijahkacb','Female','Lagos');
INSERT INTO Writer
VALUES (05,'backDavid Hassankacb','Male','Abuja');
INSERT INTO Writer
VALUES (06,'backNiniola Disukacb','Female','Lagos');
INSERT INTO Writer
VALUES (08,'backJoe Smithkacb','Male','Lagos');
-- Query 
SELECT id, name, BTRIM(name, 'back') AS new_name, gender
FROM Writer;

Explanation

In the code above:

  • Lines 1–7: We create a table called Writer with the columns id, name, gender, and state.
  • Lines 11–21: We insert data into the Writer table.
  • Lines 24–26: We remove the string appended to each name in the name column using the BTRIM() 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)