SQL stands for Structured Query Language. It is used to manage and query relational databases. In most cases, you will need to perform some operations on string data. For example, you may need to extract some characters from the string. Getting the first character of a string can be done in many different ways.
While Working on String in SQL, we need to manipulate and handle them under various conditions. we sometimes categorize the data based on the first character of the string. To get the first character from a string, we can use the SUBSTRING() function or SUBSTR() function. In this article, let us understand how to use these functions to get the first character of a string.
SUBSTRING()
SUBSTRING() is a function in SQL that can be used to get substrings from strings based on start positions and length. You can use this function to get a substring from a string column or a literal value, providing you with the flexibility to work with textual data.
Syntax:
SUBSTRING(string, start, length)
- string: This parameter represents the string from which we need to extract the part of a string. This can be the column_name of an underlying table that contains the string values.
- start: This is the starting index from which the string should start extracting.
- length: This is an optional parameter that is used to represent the terminating index of the substring process. If this parameter is not mentioned, the length parameter will be assigned as the actual length of the string by default.
Examples of Extracting First Character
Let’s us consider a table STUDENT_DETAILS which contains the details of the students along with their First_name, Last_name, and Course.
Query:
Query for creation of the table and insertion in the table
CREATE TABLE STUDENT_DETAILS (
First_name VARCHAR(10),
Last_name VARCHAR(10),
Course VARCHAR(10)
);
INSERT INTO STUDENT_DETAILS (First_name, Last_name, Course) VALUES
('Poojitha', 'Pullambhatla', 'IT'),
('Siri', 'Varma', 'CSE'),
('Sindhu', 'Botla', 'CSE'),
('Ravi', 'Suggala', 'IT'),
('Dhoni', 'Singh', 'MECH');
Output:
STUDENT_DETAILS
Example 1: Extracting First Character from First Names using SUBSTRING() in SQL
To extract first characters from the First_name of all the students, we can use the following query with SUBSTRING() function
SELECT SUBSTRING(First_Name, 1, 1) AS First_name_first_character
FROM STUDENT_DETAILS;
Output:
SQL Query to extract the first character in the First_name of all the students
Explanation: Here, the SUBSTRING() function accepts the strings in the First_name column and starting from index 1, it extracts a part of string of length 1. That is the first character of the strings in the First_name column.
Example 2: Extracting First Character from Last Names using SUBSTRING() in SQL
SQL Query: To extract first characters from the Last_name of all the students, we can use the following query with SUBSTRING() function
SELECT SUBSTRING(Last_Name, 1, 1) AS Last_name_first_character
FROM STUDENT_DETAILS;
Output:
SQL Query to extract the first character in the Last_name of all the student
Explanation: Here, the SUBSTRING() function accepts the strings in the Last_name column and starting from index 1, it extracts a part of string of length 1. That is the first character of the strings in the Last_name column.
Conclusion
SUBSTRING() is a function in SQL that can be used to manipulate string data. It can be used to get substrings according to different positions and lengths. For example, it can be used to retrieve the first character in a string. This function can be used in MySQL database, SQL Server database, or any other database that complies with SQL. It provides a standardized and flexible solution.