LEFT Function in SQL Server

by

In this SQL Server tutorial, you will learn about LEFT Function in SQL Server. You will use this function to extract the leftmost character from the value.

First, you will understand how to use the LEFT() function with literal string, then use the LEFT() function with table column. Finally, with the GROUP BY clause.

LEFT Function in SQL Server

The LEFT() function in SQL Server allows us to manipulate the data or extract the data from the given strings or values. In other words, the LEFT() function extracts the characters from the left side of the strings or values based on the number of characters that you want to extract.

It is very helpful in data cleaning, data extraction, performance optimization, concatenation, etc.

The syntax is given below.

LEFT(str_or_value, length)

Where,

  • LEFT(): It is the function for extracting the characters from the left side of the strings.
  • str_or_value: It is the value, string, or column from which you need to extract the character. This value can be of any datatype but can’t be the text and ntext.
  • length: It means the number of characters that you want to extract from the string value or expression.

To understand the workings of the LEFT() function, let’s take a simple example. Suppose you have the string ‘United States of America’ and you want to extract the 6 characters from the beginning or left side of that string.

For that use the below query.

SELECT LEFT('United States of America', 6) as LeftPart;
LEFT Function in SQL Server Extracting Leftmost Six Characters From the String

Look at the above output, the extracted part is the ‘United’ which is the 6 characters from the whole string ‘United States of America’. In the above query the length is 6 which means extract the 6 characters from the beginning or left side of the string ‘United States of America’.

You can also extract the number of digits from the numbers. For example, execute the below query.

SELECT LEFT(567398746, 5) as LeftPart;
LEFT Function in SQL Server Extracting Leftmost Five Digit From the Numbers

As you can see in the above output, the LEFT() function extracted the five-digit which is 56739 from the given number 567398746.

But in reality, the LEFT() function takes the number and first convert the number into a string and then extract the number of character from that string.

So the LEFT() function performs implicit conversion which means it converts the given value to a string datatype before extracting the characters. So as a result the extracted value 56739 is the characters, not a digit.

But if you want to convert it back to a number then you can wrap the above query with the CAST() function to convert it from string to number datatype using the below query.

SELECT CAST(LEFT(567398746, 5)as INT) as LeftPart;
LEFT Function in SQL Server Extracting Leftmost Five Digit From the Numbers Using CAST() Function

So here you can extract the leftmost character from any datatype except the text and ntext datatype. Because it can convert any datatype to a string (VARCHAR) datatype implicitly.

Until now you have learned how to use the LEFT() function with a single value (literal strings) using the SELECT statement. Let’s see how to use the LEFT() function with table columns.

LEFT Function in SQL Server with Table Column

If you have a table column that contains multiple values and want to extract the leftmost character from the table column value, you can also do that using the LEFT() function.

For example, suppose you have Staff tables with columns StaffID, FullEmployeeName which is shown below.

LEFT Function in SQL Server with Staff Table Column
SELECT StaffID, FullEmployeeName, LEFT(FullEmployeeName, 10) As ExtractedName
FROM Staff;
LEFT Function in SQL Server with Table Column

When you execute the above query it extracts the first 10 characters from the FullEmployeeName column of the Staff table as shown in the above output. For example, Riley WalK is 10 characters from an employee named Riley Walker, and the ID is equal to 10.

But the name which is less than 10 characters is not affected by the LEFT() function. Actually, that name does but contains less number of characters than the number of characters needed to be extracted.

For example, the employee with the name Tara Hill and the ID is equal to 20, after extraction contains the same number of characters.

This is how to use the LEFT() function in SQL Server with a table column.

LEFT Function in SQL Server with Group By Clause

If you want to use the LEFT() function with the GROUP BY clause to group the results based on the extracted characters, then you can do that too.

Let’s say you have ‘Products_Info’ which is shown below.

LEFT Function in SQL Server with Group By Clause Products_Info Table

You are managing an online store in the USA, and you need to perform an inventory analysis by grouping products into categories based on the first 3 characters of their names and also compute the average price. For that use the below query.

SELECT LEFT(product_name, 3) AS ProductCategory, AVG(price) AS AveragePrice
FROM Products_Info
GROUP BY LEFT(product_name, 3);
LEFT Function in SQL Server with Group By Clause

When you have executed the above query, it categorizes products based on the first three characters of the product name, and for each category, it also computes the average price.

As you can see in the above output or picture, the result shows distinct product categories and their corresponding average prices. For example, the Blu product category average price is 69,99000 dollars.

This is how to use the LEFT function in SQL Server with GROUP by clause.

Conclusion

In this SQL Server tutorial, you covered how to extract the characters from the left side of the given expression or value using the LEFT() function in SQL Server. Then how to apply the LEFT() function on the table column. After that, use the LEFT() function with the GROUP BY clause.

You may like to read: