SQL Server Left Function

The SQL Server Left Function is used to get the first n characters starting from the left-hand side of the columns on which the Left Function is applied.

The syntax of the LEFT function in SQL Server is like this:

In the above-written syntax:

The string_value is the input value that will be provided as an input to the LEFT Function and that input value can be anything either literal variable, string, or column.
Any data type can be the output of the string_value other than the TEXT or NTEXT, which is implicitly converted to VARCHAR or NVARCHAR.
The second parameter that is given as input to the LEFT Function is the number_of_characters_to_be_returned, which is a positive integer that specifies the number of characters of the string_value that will be returned as a result of the LEFT Function.
The output or result of the LEFT() function is a VARCHAR value majorly depending upon the data type of the first input parameter, i.e., string_value.

How to use LEFT Function in SQL Server?

Let us understand the usage and functioning of the Left Function in the SQL Server with the help of an example. Let us create a table named students having three columns named studID, rollNo, Name to store the student ID, roll no, and Name of the student respectively. The command for creating a table in SQL server with the above-mentioned schema will be:

CREATE TABLE students (
	studID varchar(10),
	rollNo int,
	Name varchar(30)
);

As shown in the image that we have successfully created a table having the above-mentioned schema that is three columns, two having data type as varchar and one as int.

Now, let us add some data to the students' table. The syntax of the INSERT command in SQL Server to add data in the students' table will be:

INSERT INTO students(rollNo, Name) VALUES(87459, "Andrew");
INSERT INTO students(rollNo, Name) VALUES(54786, "Samuel");
INSERT INTO students(rollNo, Name) VALUES(22149, "Nirnay");
INSERT INTO students(rollNo, Name) VALUES(94365, "Paul");
INSERT INTO students(rollNo, Name) VALUES(35479, "Casey");
INSERT INTO students(rollNo, Name) VALUES(74566, "Martin");
INSERT INTO students(rollNo, Name) VALUES(10259, "Raphel");

As we can see in the image that we have successfully added seven rows to the students' table and the same can be seen in the result of the SELECT query.

We have added data for only two columns of the students' table that are rollNo and name column, but for adding data to the studID column we will be using the LEFT Function.

The syntax that we will be using to add data to the studID column of the student table is:

So, with this query, we are automatically generating the ID of the student using the student's roll no and name of the student. The data for the studID column is not taken by the user but calculated automatically using the logic that the ID of the student will comprise of first three digits of roll no and the first three letters of the Name of the student.

As shown in the image we can see that we have successfully created the IDs of the students from the name and the rollno of the students.

This is the one scenario in which the LEFT Function can be used, another scenario can be like we want to display only the first four letters of the name column present in the students table.

The syntax for displaying the first four-letter of the Name column from the student table is:

As we can see clearly in the adobe image the first column displayed is the names of the students and the next column that is displayed is the first four letters of each data that is present in the name column of the students' table.

These two are scenarios are just for the sake of example, we can use this LEFT Function according to the requirement of the problem statement and structure the query according to it and get our desired output.

So, with the help of this article, we are able to get a better perspective of how to use the LEFT Function in the SQL server and it's working.

Next TopicSQL Server Sequence

← prev next →