April 26, 2021

Description

The SQL LEFT() function returns the specified number of characters starting from the left side of a given character expression.

LEFT

The LEFT function is used to return either the beginning or ending portion of a string.

LEFT will return the beginning characters; whereas, RIGHT is used to return the ending characters.

The general form for the LEFT function is

LEFT(value, length)

Where value is the string you’re working with, and length is the number of characters you wish to return from the beginning of value.

Using LEFT function to return beginning characters
Example of LEFT Function

If the length is greater than the number of characters in the value, then the entire string is returned.

IF length is negative an error is thrown.

If either length or value are NULL, then NULL is returned.

Suppose the Production Manager wants a distinct list of all product number prefixes.  How could you return that list?

If you look at the Production.Product ProductNumber column value you’ll notice the prefix is the first two characters.   So knowing what we do now about the LEFT function, the solution is a snap!

Here is what you can write:

SELECT DISTINCT LEFT(ProductNumber,2)
FROM   Production.Product

The LEFT function returns the first two characters.  Since we’re using the DISTICT operator, duplicate results are eliminated from the result.

OK so now the production manager is getting crazy!  The last character of the ProcuctNumber prefix means a lot to her.  She wants to know what those distinct values are.  What can we do to help?

We can use LEFT and RIGHT in conjunction.  We already know how to get the LEFT most characters, now all we need to do is take the last character from that result and return it as shown below.

LEFT and RIGHT used together.
Nesting Function to return characters.

Here is the query you can use:

SELECT DISTINCT RIGHT(LEFT(ProductNumber,2),1)
FROM   Production.Product

Like other functions you can nest string functions.  Just remember that the results of one function can be used in another.  You should read these expressions from the “inside out.”

SQL LEFT Usage Notes

  • The character expression as the first argument of the LEFT() function can be a constant string, a variable string, or a database column.
  • Integer value, as the second argument of the LEFT() function, is a positive value. If the value is negative, an error returns.
  • If the data type of character expression value is non-Unicode, the LEFT() function returns a varchar data type value. In the case of Unicode data type, it returns nvarchar data type value.

Syntax

LEFT(character_expression, integer value)

SQL LEFT Examples

We will use the AdventureWorks2019 database for all the examples.

In the following example, the LEFT() function returns the given number of characters starting from the left side of the input character string.

SELECT LEFT('first 5 characters', 5) outputstring;
SELECT LEFT('first 5 characters', 5) outputstring;

The query returns ‘first’ as the leftmost five characters in the literal string.

In the second example, we pass the FirstName column of the Person table as our first argument. The SQL LEFT() function returns the leftmost five characters of each FirstName column value.

SELECT LEFT(FirstName, 5) firstname FROM Person.Person;
/* Answer */
SELECT LEFT(FirstName, 5)  firstname 
FROM Person.Person;

Next, let’s use LEFT() with an order by clause. We select the vendor name using SQL LEFT to select the leftmost six characters of the vendor name. The order by clause orders them in decreasing values of the credit ratings.

SELECT LEFT(Name, 6) VendorName, CreditRating FROM Purchasing.Vendor ORDER BY CreditRating desc;
/* Answer */
SELECT LEFT(Name, 6) VendorName, CreditRating
FROM   Purchasing.Vendor
ORDER BY CreditRating desc;

This query returns the six characters from the left side of every vendor’s name and orders them from highest to lowest credit rating.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>