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.
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.
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.
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.
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.
This query returns the six characters from the left side of every vendor’s name and orders them from highest to lowest credit rating.
