2

How to Remove Prefix From Field in SQL Server?

 11 months ago
source link: https://www.geeksforgeeks.org/how-to-remove-prefix-from-field-in-sql-server/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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:

gfg1.jpeg

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

Query:

USE GeeksForGeeks

Output:

gfg2.jpeg

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:

WhatsAppImage20211118at185852.jpeg

Step 4: Describe the structure of the table FLIGHT.

Query:

EXEC SP_COLUMNS FLIGHT;

Output:

WhatsAppImage20211118at185915.jpeg

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:

WhatsAppImage20211118at190243.jpeg

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

Query:

SELECT * FROM FLIGHT;

Output:

WhatsAppImage20211118at190308.jpeg

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:

WhatsAppImage20211118at190413.jpeg

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

Query:

SELECT * FROM FLIGHT;

Output:

WhatsAppImage20211118at190429.jpeg
Last Updated : 28 Nov, 2021
Like Article
Save Article

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK