How to Get First Character of a String in SQL?

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

The 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

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.

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

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.