How to Remove the First Characters of a Specific Column in a Table in SQL?

Here we will see, how to remove the first characters of a specific column in a table in SQL. We can do this task using the String function. 

String functions are used to perform an operation on an input string and return an output string. There are various string functions like LEN(for SQL server), SUBSTR, LTRIM, TRIM, etc. To perform the required function we need the SUBSTRING() functions

SUBSTRING(): 

This function is used to find a sub-string from the string from the given position. It takes three parameters:  

• String: It is a required parameter. It provides information about the string on which function is applied.
• Start: It gives the starting position of the string. It is also the required parameter.
• Length: It is an optional parameter. By default, it takes the length of the whole string.

Query:

SELECT SUBSTRING('w3wiki', 1, 5);

Output:

To delete the first characters from the field we will use the following query:

Syntax:

SELECT SUBSTRING(string, 2, length(string));

Here, string denotes the field, 2 denotes the starting position of string i.e second character, and length(string) denotes the length of the string. 

Query:

SELECT SUBSTRING('w3wiki', 2, len('w3wiki'));

Output:

For the purpose of the demonstration, we will be creating Beginner for Beginner table in a database called “Beginner“.

Step 1: Creating the Database

Use the below SQL statement to create a database called Beginner.

Query:

CREATE DATABASE Beginner;

Step 2: Using the Database

Use the below SQL statement to switch the database context to Beginner.

Query:

USE Beginner;

Step 3: Table definition

 We have the following Beginner for Beginner in our geek’s database.

Query:

 CREATE TABLE w3wiki
 (FIRSTNAME VARCHAR(20),
 LASTNAME VARCHAR(20),
 AGE INT,GENDER VARCHAR(20));

Step 4: Insert data into the table

INSERT INTO w3wiki VALUES
('Romy', 'Kumari', 22, 'female'),
('Pushkar', 'Jha', 23, 'male'),  
('Meenakshi', 'Jha', 20, 'female'),
('Shalini', 'Jha', 22, 'female'),
('Nikhil', 'Kalra', 23, 'male'),
('Akanksha', 'Gupta', 23, 'female');

Step 5: See the content of the table

 Use the below command to see the content of the Beginner for Beginner table:

Query:

SELECT * FROM w3wiki;

Output:

Step 6: Now to delete the first character from the field we will use the Beginner for Beginner table. Below is the syntax for the SUBSTRING() function to delete the first character from the field.

Syntax:

SELECT SUBSTRING(column_name,2,length(column_name))
FROM table_name;

To delete the first character from the FIRSTNAME column from the Beginner for Beginner table. We use the given below query:

Query:

SELECT SUBSTRING(FIRSTNAME,2,len(FIRSTNAME))
FROM w3wiki;

Output:

Now to delete the first character from the LASTNAME column.

Query:

SELECT SUBSTRING(LASTNAME,2,len(LASTNAME))AS
LASTNAME FROM w3wiki;

Output: