How to Remove Prefix From Field in SQL Server?

In SQL, certain words are reserved. These are called Keywords or Reserved Words. These words cannot be used as identifiers i.e. as column names in SQL. But, there is an exception to this rule too. In this article, we will discuss how to use Reserved Words as column names in SQL and how to remove prefixes from a field. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table of FLIGHT inside the database GeeksForGeeks. This table has 3 columns namely PASSENGER_NAME, SEAT_NO and DESTINATION containing the names, seat number, salaries, and destination of the passengers traveling in a flight.

Query:

CREATE TABLE FLIGHT(
PASSENGER_NAME VARCHAR(20),
SEAT_NO INT,
DESTINATION VARCHAR(10));

Output:

Step 4: Describe the structure of the table FLIGHT.

Query:

EXEC SP_COLUMNS FLIGHT;

Output:

Step 5: Insert 5 rows into the FLIGHT table.

Query:

INSERT INTO FLIGHT VALUES('MR. MR. VINAYAK',11,'DEL');
INSERT INTO FLIGHT VALUES('MR. MR. SINGH',06,'BOM');
INSERT INTO FLIGHT VALUES('MR. MR. KHAN',32,'KOL');
INSERT INTO FLIGHT VALUES('MR. MR. SHARMA',25,'CHD');
INSERT INTO FLIGHT VALUES('MR. MR. KUMAR',16,'LKO');

Output:

Step 6: Display all the rows of the FLIGHT table.

Query:

SELECT * FROM FLIGHT;

Output:

Step 7: As evident from the FLIGHT table, an extra MR. has been added at the start of all entries of the PASSENGER_NAME column due to clerical error. To remove this prefix of ‘MR. ‘, we need to use the keywords UPDATE, SET, RIGHT, LEN, and WHERE. The following command updates the entry of the column starting with ‘MR. ‘ with a substring of the name extracted using RIGHT keyword. The substring starts from an index equal to the length of the prefix(length of ‘MR. ‘=4) to the end of the string.

Syntax:

UPDATE TABLE_NAME
SET COLUMN_NAME = RIGHT(COLUMN_NAME,LEN
COLUMN_NAME)-LENGTH OF PREFIX TO BE REMOVED)
WHERE COLUMN_NAME LIKE 'PREFIX%';

Query:

UPDATE FLIGHT
SET PASSENGER_NAME = RIGHT
(PASSENGER_NAME,LEN(PASSENGER_NAME)-4)
WHERE PASSENGER_NAME LIKE 'MR. %';

Output:

Step 8: Display all the rows of the corrected FLIGHT table.

Query:

SELECT * FROM FLIGHT;

Output: