How to Get First Character of a String in SQL?
Last Updated :
05 Dec, 2024
SQL (Structured Query Language) is essential for managing and querying relational databases. Whether you're handling customer data, employee records, or product details, SQL provides powerful tools for manipulating string data. One common task when working with strings is to extract the first character from a string. This can be useful for categorizing data, sorting records, or performing additional data analysis.
In this article, we will explain how to extract the first character of a string in SQL using two popular methods: the SUBSTRING() and SUBSTR() functions. We'll provide examples, explain their usage, and ensure we have everything needed to implement these techniques in our SQL queries.
Using SQL to Extract the First Character of a String
When working with string data in SQL, extracting specific portions of a string, such as the first character, is a common requirement. To perform this task, we can use two primary SQL functions: SUBSTRING() and SUBSTR(). Both of these functions can be used to extract a substring from a string, and we'll focus on using them to get the first character.
1. SUBSTRING()
SUBSTRING() is a function in SQL that can be used to get substrings from strings based on start positions and length. We can use this function to get a substring from a string column or a literal value, providing us with the flexibility to work with textual data.
Syntax
SUBSTRING(string, start, length)
Key Terms
- 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.
Example 1: Extracting the First Character Using SUBSTRING()
Let's us consider a table STUDENT_DETAILS which contains the details of the students along with their First_name, Last_name, and Course. We'll use the SUBSTRING() function to extract the first character from the First_name
column.
Table Structure and Sample Data
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_DETAILSThe query will return the first character of each student's first name from the STUDENT_DETAILS table.
Query:
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 studentsExplanation:
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.
2. SUBSTR() Function
The SUBSTR() function works similarly to SUBSTRING(), but it's often used in certain database systems like Oracle and MySQL. It's another useful method for extracting substrings from a string.
Syntax
SUBSTR(string, start, length)
Example 2: Extracting First Character from Last Names using SUBSTRING() in SQL
To extract first characters from the Last_name of all the students, we can use the following query with the SUBSTRING() function. This technique helps us quickly retrieve the first letter of a person's last name, which can be useful for grouping or sorting data based on initials.
Query:
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 studentExplanation:
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.
Similar Reads
How to get the last character of a string in PHP ?
In this article, we will find the last character of a string in PHP. The last character can be found using the following methods. Using array() Method: In this method, we will find the length of the string, then print the value of (length-1). For example, if the string is "Akshit" Its length is 6, i
2 min read
How to remove First and Last character from String in Scala?
In this article, we will explore different approaches to removing the first and last character from a string in Scala. Table of Content Using substring methodUsing drop and dropRight methodsUsing substring methodIn this approach, we are using the substring method which takes two arguments the starti
1 min read
How to remove the first character of string in PHP?
Remove the very first character of a given string in PHP Examples: Input : GeeksforgeeksOutput : eeksforgeeksInput :, Hello geek!Output : Hello geek!Explanation: In PHP to remove characters from the beginning we can use ltrim but in that, we have to define what we want to remove from a string i.e. r
3 min read
How to get the position of character in a string in PHP ?
In this article, we will get the position of the character in the given string in PHP. String is a set of characters. We will get the position of the character in a string by using strpos() function. Syntax: strpos(string, character, start_pos) Parameters: string (mandatory): This parameter refers t
2 min read
Count no. of characters and words in a string in PL/SQL
Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given a string and the task is to count the number of c
2 min read
Delete First Character of a String in TypeScript
Deleting the first character of a string in Typescript consists of removing the character at the 0th index of the string. Using various inbuilt methods, we can extract the first character of the input string and print or return only the remaining characters apart from the first character. There are
2 min read
How to Remove Last Character from String in Ruby?
Removing the last character from a string in Ruby is a common task in various programming scenarios. Whether you need to manipulate user input, process file paths, or clean up data, there are several approaches to achieve this. This article focuses on discussing how to remove the last character from
2 min read
How to Remove the Last Character From a Table in SQL?
SQL (Structured Query Language) allows for efficient data manipulation and retrieval. A common task in SQL involves removing the last character from a specific column within a table. This can be achieved using string functions like SUBSTRING() and LEN(). In this article, we will demonstrate how to a
5 min read
How to Get substring from a column in PySpark Dataframe ?
In this article, we are going to see how to get the substring from the PySpark Dataframe column and how to create the new column and put the substring in that newly created column. We can get the substring of the column using substring() and substr() function. Syntax: substring(str,pos,len) df.col_n
3 min read
Delete first character of a string in JavaScript
To delete the first character of a string in JavaScript, you can use several methods. Here are some of the most common ones Using slice()The slice() method is frequently used to remove the first character by returning a new string from index 1 to the end. [GFGTABS] JavaScript let s1 = "Geeksfor
1 min read